Copying live worksheets

Platinum Contributor

Jun 29, 2023

If the source and destination workbooks are in different folders on the same network, you can modify the code to include the full file paths for both workbooks.

Here is an updated version of the code:

Sub CollateDataFromWorkbooks()
    Dim sourceFolderPath As String
    Dim destinationWorkbookPath As String
    Dim destinationWorkbook As Workbook
    Dim sourceWorkbookFile As String
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim destinationWorksheet As Worksheet
    Dim nextRow As Long
    
    ' Set the folder path for the source workbooks
    sourceFolderPath = "Path\to\source\folder\"
    
    ' Set the file path for the destination workbook
    destinationWorkbookPath = "Path\to\destination\workbook.xlsx"
    
    ' Open the destination workbook
    Set destinationWorkbook = Workbooks.Open(destinationWorkbookPath)
    
    ' Loop through the files in the source folder
    sourceWorkbookFile = Dir(sourceFolderPath & "*.xlsx")
    Do While sourceWorkbookFile <> ""
        ' Open each source workbook
        Set sourceWorkbook = Workbooks.Open(sourceFolderPath & sourceWorkbookFile)
        
        ' Set the source and destination worksheets
        Set sourceWorksheet = sourceWorkbook.Worksheets("SourceWorksheetName")
        Set destinationWorksheet = destinationWorkbook.Worksheets("DestinationWorksheetName")
        
        ' Find the next available row in the destination worksheet
        nextRow = destinationWorksheet.Cells(destinationWorksheet.Rows.Count, 1).End(xlUp).Row + 1
        
        ' Copy the data from the source worksheet to the destination worksheet
        sourceWorksheet.UsedRange.Copy destinationWorksheet.Cells(nextRow, 1)
        
        ' Close the source workbook without saving changes
        sourceWorkbook.Close SaveChanges:=False
        
        ' Get the next file in the source folder
        sourceWorkbookFile = Dir
    Loop
    
    ' Close the destination workbook with saving changes
    destinationWorkbook.Close SaveChanges:=True
    
    ' Clear the clipboard to remove any formatting or data stored there
    Application.CutCopyMode = False
End Sub

In this code, you need to specify the folder path where the source workbooks are located using the sourceFolderPath variable. The code will loop through all the Excel files in the specified folder, open each source workbook, copy the data from the desired worksheet, and paste it into the destination worksheet in the destination workbook.

Make sure to replace "Path\to\source\folder" with the actual folder path where your source workbooks are located, and specify the names of the source and destination worksheets.

Please note that this code assumes all the source workbooks have the same structure and contain the desired worksheet with the data you want to collate.

Make sure beforehand with your administrator that VBA is allowed in your network and not blocked.

Finally, please keep in mind that this can only be a template VBA code, since there is no detailed data about the digital environment of the project.

Hope this will help you.

Forum Discussion