Forum Discussion
VBA copy data from another workbook (specific range) into current workbook (specific tab)
- Nov 14, 2023
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.
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!
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
- Kitty_Cat86Nov 16, 2023Copper ContributorOh 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.- NikolinoDENov 17, 2023Gold Contributor
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:
- It finds the last row again after pasting the data and file name to accurately determine the current last row.
- 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.