VBA copy data from another workbook (specific range) into current workbook (specific tab)

Kitty_Cat86 

You can accomplish this task with a VBA macro. This script will loop through files in a specified folder, copy data from a specific sheet and range, then paste it into your "Data" tab in the master workbook.

Here's a sample script to get you started:

Sub ConsolidateData()
    Dim mainWB As Workbook
    Dim wsMaster As Worksheet
    Dim folderPath As String
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim lastRow As Long
    
    'Set your master workbook
    Set mainWB = ThisWorkbook
    
    'Set the master "Data" worksheet
    Set wsMaster = mainWB.Sheets("Data")
    
    'Specify the folder path where your source workbooks are located
    folderPath = "C:\Your\Folder\Path\" 'Change this to your folder path
    
    'Check if the folder path ends with a backslash, if not, add it
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If
    
    'Get the first file in the folder
    Dim filename As String
    filename = Dir(folderPath & "*.xlsx")
    
    'Loop through all files in the folder
    Do While filename <> ""
        'Open the workbook
        Set sourceWB = Workbooks.Open(folderPath & filename)
        'Set the source worksheet (change "Journal Entries" to your sheet name)
        Set sourceWS = sourceWB.Sheets("Journal Entries")
        
        'Find the last used row in the source worksheet
        lastRow = sourceWS.Cells(sourceWS.Rows.Count, "B").End(xlUp).Row
        
        'Copy range from source worksheet
        sourceWS.Range("B6:P" & lastRow).Copy
        
        'Find the last row in the master "Data" sheet
        lastRow = wsMaster.Cells(wsMaster.Rows.Count, "B").End(xlUp).Row
        
        'Paste into the master "Data" worksheet
        wsMaster.Cells(lastRow + 1, 2).PasteSpecial xlPasteValues 'Pasting starting from column B
        
        Application.CutCopyMode = False 'Clear clipboard
        
        sourceWB.Close SaveChanges:=False 'Close the source workbook without saving
        
        filename = Dir 'Move to the next file in the folder
    Loop
End Sub

Please adjust the folderPath variable to point to the folder where your source workbooks are located. Also, update the sheet names ("Journal Entries", "Data") as per your actual sheet names.

This script loops through all .xlsx files in the specified folder, opens each file, copies the range from the "Journal Entries" sheet, and pastes it into the "Data" tab of your master workbook, one after the other.

Run this macro in your master workbook. This script assumes that all the source workbooks are in .xlsx format. Adjust as needed based on your actual file format and folder structure. In the provided script, the macro opens each workbook in the specified folder one at a time, copies the data, and then closes the workbook without saving changes. This means that the script processes the data without keeping all workbooks open simultaneously.The text and the code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.