Forum Discussion

Kitty_Cat86's avatar
Kitty_Cat86
Copper Contributor
Nov 13, 2023

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

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 macr...
  • NikolinoDE's avatar
    Nov 14, 2023

    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.

Share

Resources