VBA - Copy from multiple workbooks to single workbook with corresponding sheets

[Eric Milligan]

I have a somewhat specific issue that I haven’t found an answer to on here or anywhere else

Every day, I have 89 workbooks of exported info whose data needs to be added to a single daily master workbook. The tricky part is that the master workbook has a separate sheet for each of the 89 exports.

There are a couple details that might make this easier:
Exported workbooks
- Each day's exported workbooks have their own folder
- Each exported workbook in the series is named the same every day (i.e. the first is always named “1storder”, second is “2ndorder”, etc.)
- Each exported workbook contains a single sheet with the same name as the workbook
- The sheets contain data in columns A through P with a varying number of rows (not table formatted)
Master workbook
- The master workbook has a sheet for each of the 89 exports
- The each sheet's name corresponds to the workbook who’s data goes into it (i.e. first is “1storder”, second is “2ndorder”, etc.)

Is there a method by which a Macro can do this? Please let me know if I can provide any other info?

 

[mumps]

This macro will prompt you to choose the appropriate folder. It assumes that the source files have an "xlsx" extension.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, srcWB As Workbook, FolderName As String
    Set desWB = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       FolderName = .SelectedItems(1) & "\"
    End With
    ChDir FolderName
    strExtension = Dir("*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(FolderName & strExtension)
        With desWB.Sheets(ActiveSheet.Name)
            Range("A1", Range("P" & Rows.Count).End(xlUp)).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Eric Milligan]

This macro will prompt you to choose the appropriate folder. It assumes that the source files have an "xlsx" extension.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, srcWB As Workbook, FolderName As String
    Set desWB = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       FolderName = .SelectedItems(1) & "\"
    End With
    ChDir FolderName
    strExtension = Dir("*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(FolderName & strExtension)
        With desWB.Sheets(ActiveSheet.Name)
            Range("A1", Range("P" & Rows.Count).End(xlUp)).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

This worked. Thank you for your time on this. I greatly appreciate it.

 

[mumps]

You are very welcome.

 

[Nuppu]

This macro will prompt you to choose the appropriate folder. It assumes that the source files have an "xlsx" extension.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, srcWB As Workbook, FolderName As String
    Set desWB = ThisWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       FolderName = .SelectedItems(1) & "\"
    End With
    ChDir FolderName
    strExtension = Dir("*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(FolderName & strExtension)
        With desWB.Sheets(ActiveSheet.Name)
            Range("A1", Range("P" & Rows.Count).End(xlUp)).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

What if i have 5 workbooks in a folder that has again multiple sheets and i need to merge data from one particular sheet from every workbook to another master worksheet... the data in sheet is not in same order!

 

[Fluff]

@Nuppu As your question is significantly different than this thread, please start a new thread for your question.
Also please read the rules, especially Rule#12 regarding duplicate questions.