Create Master Workbook From Multiple Workbooks

[jo15765]

Hello - I need to iterate each workbook in a given directory and copy the data from the first worksheet starting on row 2 'til the end of the worksheet to a "master" workbook. This is psuedocode of what I need to do
1) Create a new .xlsx called Master
2) Open first workbook in C:\Test\ and copy all rows from row 2 -> end of workbook
3) Paste data in Master workbook starting on row 2
4) Open second workbook in C:\Test\ and copy all rows from row 2 -> end of workbook
5) Paste data in Master workbook starting on the first empty row (of course would change each paste)
6) Rinse and repeat until all workbooks have been opened and copy/pasted
7) Save Master workbook

 

[Gokhan Aycan]

Since you won't be writing to the source workbooks, you should be able to get data without "opening" (so to speak) workbooks. MrExcel Forum Link

The following will let you get folder list (just one way of doing it). and will require a reference to Microsoft Scripting Runtime.

Public Sub test_folder_list()

    Dim fso As FileSystemObject
    Dim oFolder As Folder
    Dim oFiles As Files
    Dim oFile As File
   
    Set fso = New FileSystemObject
    Set oFolder = fso.GetFolder("C:\Test")
    Set oFiles = oFolder.Files
   
    For Each oFile In oFiles
        Debug.Print fso.GetBaseName(oFile.Name), fso.GetExtensionName(oFile.Name), oFile.Name, oFile.Type
    Next oFile
   
    Set oFiles = Nothing
    Set oFolder = Nothing
    Set fso = Nothing
   
End Sub

Rest is managing the ranges and keeping the next empty row number on master.
You can get your ranges using Range().CurrentRegion, Worksheets().UsedRange, or the usual Range().End(xlUp).Row methods etc.