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

[Kitty_Cat86]

Hi all,

Struggling to find (or chop and change a macro to suit my needs). I have a workbook (let's say it's called master workbook). Within that workbook I have a tab called "Data". I need a macro to go into another folder where there will be multiple other workbooks (the number of these can vary month to month so needs to be on a loop that will cycle through all of these other workbook within that folder); copy from a tab named "Journal Entries", range B6:P6 downwards (there can be a varying degree of lines in each source data workbook so just need it to copy to the end of the data on these columns) then paste into my master workbook "Data" tab one after the other. It sounds so complicated writing it down but I'm sure there is an answer.

Been playing with merging workbook macros and copy data macros to no avail. Appreciate the responses 🙂 

[#2 · Accepted solution]

@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.

[#3]

You absolute superstar! Genuinely worked a treat!!! Thank you, thank you, thank you. I have one more complexity that you might be able to offer assistance on? When I copy my data into the "Data" tab, I'd like it to populate another column with the name of the file it has copied from. So we are copying cell ranges B6:P into a my master workbook into cell A1 downwards. B becomes column A, C becomes column B and so forth so P would be O; column 15. So in the next free column which would be column 16, I'd like it to paste the name of the file it has copied from in all the rows it copies over. Does that make sense? It there a good wee trick we could build into this macro to enable that? Really appreciate your assistance.

[#4]

@Kitty_Cat86 

Thank you for your feedback...You can modify the code to include the file name in the next available column (column 16) for each row that is being copied.

Here is an updated version of your code:

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
    Dim filename As String
    
    ' 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
    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 starting from column B
        wsMaster.Cells(lastRow + 1, 2).PasteSpecial xlPasteValues ' Pasting starting from column B
        
        ' Add file name in the next available column (column 16)
        wsMaster.Range(wsMaster.Cells(lastRow + 1, 16), wsMaster.Cells(lastRow + lastRow - 5, 16)).Value = filename
        
        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

This modification adds the file name (without the path) to column 16 for each row that is being copied. Make sure to adjust the column numbers if your counting is different (e.g., if your data starts from column A).

[#5]

This is working a treat. I have been trying to update it slightly so that it pastes into my master file starting from column A and the name of the file being entered into the next available column but it keeps breaking the macro 😞 This is what I'm trying:-

' Paste into the master "Data" worksheet starting from column A
wsMaster.Cells(lastRow + 1, 1).PasteSpecial xlPasteValues ' Pasting starting from column A

' Add file name in the next available column (column 15)
wsMaster.Range(wsMaster.Cells(lastRow + 1, 15), wsMaster.Cells(lastRow + lastRow - 5, 15)).Value = filename

Just seems to then copy over only 5 rows of data.

I appreciate your help so much!

@NikolinoDE 

[#6]

@Kitty_Cat86 

Replace this line:

wsMaster.Range(wsMaster.Cells(lastRow + 1, 15), wsMaster.Cells(lastRow + lastRow - 5, 15)).Value = filename

with this line:

wsMaster.Range(wsMaster.Cells(lastRow + 1, 15), wsMaster.Cells(lastRow + lastRow, 15)).Value = filename

[#7]

Oh my goodness, I can't thank you enough. There only appears to be two data inconsistencies. When pasting in the first name it only pastes it against the very first row, leaves a big gap and then starts pasting the file name in every row correctly after the first file. So say the first file data had 20 rows, it only pastes the file name into row 1 and when when the new file name starts on row 21 onwards it then pastes in every single file name after that. Very odd?

Final point being it doesn't end the file name loop so when I get to the final file name it pastes for thousands of rows and it needs to know when to end on the final, final row?

Thank you.

[#8]

@Kitty_Cat86 

It seems there's an issue with how the file names are being pasted. To address this and also make sure the loop ends properly, you can modify the code like this:

Replace the lines related to pasting the file name with the following:

' Add file name in the next available column (column 15)
wsMaster.Range(wsMaster.Cells(lastRow + 1, 15), wsMaster.Cells(lastRow + lastRow, 15)).Value = filename

' Find the last row again after pasting the data and file name
lastRow = wsMaster.Cells(wsMaster.Rows.Count, "B").End(xlUp).Row

' Increment lastRow by 1 to get the next available row for the next iteration
lastRow = lastRow + 1

This modification does a couple of things:

1. It finds the last row again after pasting the data and file name to accurately determine the current last row.
2. It increments lastRow by 1, so the next iteration starts from the next available row in the master sheet.

This should resolve the issue of inconsistent file name pasting and ensure the loop properly ends after processing all files.

[#9]

@NicolinoDE
Hello,
I have the same tasks but I have difficulties with my VBA. Is there a way you can help me get this task working?
Thanks advance!

[#10]

 

@TTC-BlueHill1