Need help pulling data from several identical workbooks and combining into one sheet.

Suggest you re-upload with sample data included. Showing sheets to be consolidated and what the master consolidated looks like. With the current presentation, I have no idea what you have and what you want.

I'm sorry. I cannot see the correlation between the two files. The data is not the same. How do you know that the information in Book1 goes into column K in Book2. This is not making any sense to me. I am needy. Please describe the business process that you are trying to accomplish in simple English terms. Explain it to me as if we are standing in line at a coffee shop.

Also, will you combining worksheets from one workbook or one sheet from different workbooks. Very confused

Originally Posted by taylorsm

Perfect world. I run my macro and it goes into my folder. It then combines all sheet 1s together, then combines all sheet 2s together, and all sheet 3s together into one workbook. This is step (sheet) one.

We can do this. One has to assume:

1) The files are located in a given folder
2) The sheets(1) in every file are the exact same format
3) The sheets(2) in every file are the exact same format
etc...

I can construct the "merge sheet1" macro, but if we see examples of workbooks with the multiple sheets then the macro could be more diverse from the start.

Meanwhile, I concur with my colleagues. It would be significantly simpler if each employee simply worked from their own file with all data starting in column A. All files would be the same. The employee's unique name taken from the filename or such. The current layout for entry and the proposed "merged" format both seem cumbersome to maintain and utilize.

Simply gathering everyone's data together into a single database format is superior in every respect. The data can be easily filtered for any one employee, or more. Pivots, reporting, everything is simple and easy.

Can't help thinking you'd make life a lot easier for yourself if you just had one workbook for each employee in a common folder. Then you could collate the data from all the employee workbooks.

So use eg. Main workbook and call from main to every other workbook by PowerQuery. It will make tables on each workbook then on main you can load every table where you want.
You can load only these columns what you decide to load but if you want to see whole tables from each client your main wokbook will be big.

After all you need to refresh only your tables on main workbook to see new data from others. (simple version )
Client's workbooks can be added one-by-one or from folder ie. all-at-once

I think you will need reorganize your sources a little

btw. it doesn't work without PQ

JB Thanks. I think i follow some. This is what I created this morning based on some online samples. Each employee starts in column A. The destination it copies to is the only difference.

Attached is a workbook that I will use to pull the data to. The employees will have the exact same, except on the "prod totals" sheet, they will only have their five columns. I have a third sheet that I will need to do but haven't even started thinking of that one yet.

Sub ProdTotal()
Dim z As Workbook
Dim a As Workbook
Dim b As Workbook
Dim c As Workbook
Dim d As Workbook
Dim e As Workbook
Dim f As Workbook
Dim g As Workbook
Dim h As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False


'## Open workbooks first:
Set z = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\Master")
Set a = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\a1")
Set b = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\b1")
Set c = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\c1")
Set d = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\d1")
Set e = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\e1")
Set f = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\f1")
Set g = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\g1")
Set h = Workbooks.Open("C:\Users\taylorsm\Desktop\Combined\QARs\h1")


Dim aRng As Range
Dim bRng As Range
Dim cRng As Range
Dim dRng As Range
Dim eRng As Range
Dim fRng As Range
Dim gRng As Range
Dim hRng As Range

Set aRng = a.Sheets("Prod Totals").Range("A1:E300")
Set bRng = b.Sheets("Prod Totals").Range("A1:E300")
Set cRng = c.Sheets("Prod Totals").Range("A1:E300")
Set dRng = d.Sheets("Prod Totals").Range("A1:E300")
Set eRng = e.Sheets("Prod Totals").Range("A1:E300")
Set fRng = f.Sheets("Prod Totals").Range("A1:E300")
Set gRng = g.Sheets("Prod Totals").Range("A1:E300")
Set hRng = h.Sheets("Prod Totals").Range("A1:E300")


'Now, transfer values
aRng.Copy
z.Sheets("Prod Totals").Range("A1").PasteSpecial

bRng.Copy
z.Sheets("Prod Totals").Range("F1").PasteSpecial

cRng.Copy
z.Sheets("Prod Totals").Range("K1").PasteSpecial

dRng.Copy
z.Sheets("Prod Totals").Range("P1").PasteSpecial

eRng.Copy
z.Sheets("Prod Totals").Range("U1").PasteSpecial

fRng.Copy
z.Sheets("Prod Totals").Range("Z1").PasteSpecial

gRng.Copy
z.Sheets("Prod Totals").Range("AE1").PasteSpecial

hRng.Copy
z.Sheets("Prod Totals").Range("AJ1").PasteSpecial


'close :
a.Close
b.Close
c.Close
d.Close
e.Close
f.Close
g.Close
h.Close


'Close z (not closing):
z.Activate
Columns.AutoFit
z.Save


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

If your macro does what you need, perhaps we should just leave it be.