Pulling Data from Multiple Workbooks into a Separate Master Workbook

[awebster]

Hello,

I am working on a project where I have to pull data from about 40 different workbooks into a separate data-workbook. However, each workbook is continually adding new sheets. What I would like to happen is for each time I add a new sheet to one of the workbooks, to have a fast and efficient way to add the data I need to the master data-workbook. I am posting this question in this forum because I was hoping there would be a way to have a Macro in place to transfer over the data. If more information is need on the project to find an answer I would be happy to add on.

Thank you in advance for any and all help!

[kunaaln]

Can you send a sample of the 40 workbooks (just 1 with few sheets, other with added sheets). Also a sample of Master workbook.

[awebster]

Thank you so much for your reply and willingness to help! Unfortunately, because of where I work, it makes sharing copies a little tricky. However, I think I may have stumbled upon a way to do what I needs by using VBA. If it does work, I'll update this post so others can have this information as well.

Thank you again!

[awebster]

In VBA, this is the code I am using... can anyone see what the issue is? When I go to run it, nothing happens. I don't receive an error message but my master spreadsheet doesn't update. Please help!

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("X:\zzMaster\")

Do While Len(MyFile) > 0
If MyFile = "zzMaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile)
Range("B1:L1").Copy
ActiveWorkbook.Close

erow = Data.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Data").Range(Cells(erow, 1), Cells(erow, 11))

MyFile = Dir

Loop

End Sub