Condensing 5 sheets into 1?

B

bobbly_bob

Alrighty, what we have here is 5 individuall reports that are created
by 5 different people here in our office.

The layout is identical on each of the 5 sheets, what I want though is
for there to be a master automatically filling out using all 5 sheets.
At the moment, at the end of the month we cut and paste the info from
all 5 sheets and put them all together, but I'm working on getting
running stats, so I need this to be compiled automatically. These will
all be seperate sheets of the same book btw

Any help appreciated

-Bob
 
V

Viquar

Go to Data --> Consolidate

Select the Five ranges containing the data to be consolidated
Remove the tick from Links check box if you do not want the links to the
source data

Tick the Labels in top row and Left COlumn

This should help

If you want you can also have a macro to achieve this....

Just make sure that the five tables are in the same format
I'm assuming that your data starts in A1 in each file and all these files
lie in the same directory

Sub DoConsolidate()
set newWB = workbooks.add
With Application.FileSearch
.NewSearch
.LookIn = "C:\Work\" ' Replace this with your directory
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute

For i = 1 To .FoundFiles.Count
set wb = workbooks.open(.FoundFiles(i))
set rng= wb.Sheets(1).Range("A1").currentRegion.offset(1,0)
rng.copy
newwb.sheets(1).Range("A65536").end(xlup).offset(1,0).Pastespecial
xlpasteAll
Application.CutCopyMode = False
wb.close SaveChanges:=False
Next i
End Sub
 
B

bobbly_bob

Thanks guys, looks like this might be what I want,

I'll check it out tomorrow when I'm back at work,

Cheers, I love this group
 
Top