Consolidating Several Excel Files

[Darin Kramer]

Hi...

I am about to send out a template (Say file A) to many users, who will
fill details in the template and then send back to me.

I have a summary tab (Say Tab bb) in the template going out that
summarises all data contained within the template.

Once I recieve all the File A's back (approx 100) I need to consolidate
the tabs (ie Tab bb) , ie compare the submissions side by side in one
workbook
Is there any easier way than copying and pasting the sheet 100 times)
Any consolidation type process that can automatically be run by a Macro?
Anything I need to add to my File A (or Tab bb) sheet before sending
out...?

Thanks and Regards

D

 

[Tom Ogilvy]

assume you put all your files in a single directory

Dim i as Long, sName as String, sh as Worksheet
Dim dest as Range, bk as Workbook
i = 1
sName = dir("C:\MyResultsFiles\*.xls")
do while sName <> ""
set bk = workbooks.Open("C:\MyResultsFiles\" & sName)
set sh = bk.worksheets("Tab bb")
set dest = workbooks("Output.xls).Worksheets(1).cells(1,i)
i = i + 1
sh.Columns(1).copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName
' close the workbook
bk.close SaveChanges:=False
sName = dir()
Loop

Of course if all the workbooks have the exact same name, this wouldn't work.

 

[Darin Kramer]

Thanks Tom.

Where do I put that VBA. Into a module of a blank consolidated book? How
do I "run" it??

Appreciate your help....

D

 

[Darin Kramer]

I just created a normal sub, and it runs, but I get an error (Script out
of Range) on the line

"Set dest = Workbooks("Consol2.xls").Worksheets(1).Cells(1, i)"

Does the sheet conso2.xls have to be in the same sub directory, or does
there need to be a sheet at all (ie is it created or must it be pre
created? "


Thanks

D

 

[Darin Kramer]

Tom,

I also need to copy columns 1 to 3 instead of just one.

Whats the VB for that sh.columns(1,3) copy?

Thanks

D

 

[Tom Ogilvy]

As written, you could put it in any workbook except one of the workbooks to
be process - however, I have modified it to be placed in a blank
consolidation workbook.

Sub DarinsConsolidator()
Dim i as Long, sName as String, sh as Worksheet
Dim dest as Range, bk as Workbook
i = 1
sName = dir("C:\MyResultsFiles\*.xls")
do while sName <> ""
set bk = workbooks.Open("C:\MyResultsFiles\" & sName)
set sh = bk.worksheets("Tab bb")
set dest = ThisWorkbook.Worksheets(1).cells(1,i)
i = i + 1
sh.Columns(1).copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName
' close the workbook
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

then put the above in a normal Module (insert module) in that workbook.
Then go to Tools=>Macro=>Macros, select DarinsConsolidator and hit run.

 

[Tom Ogilvy]

As shown, Consol2.xls is the name of the workbook, not the worksheet and the
workbook must be open. the Worksheets(1) refers to the sheet - the first
sheet in the tab order in Consol2.xls regardless of name.

 

[Tom Ogilvy]

Sub DarinsConsolidator()
Dim i as Long, sName as String, sh as Worksheet
Dim dest as Range, bk as Workbook
i = 1
sName = dir("C:\MyResultsFiles\*.xls")
do while sName <> ""
set bk = workbooks.Open("C:\MyResultsFiles\" & sName)
set sh = bk.worksheets("Tab bb")
set dest = ThisWorkbook.Worksheets(1).cells(1,i)
i = i + 3
sh.Columns(1).Resize(,3).copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName
' close the workbook
bk.close SaveChanges:=False
sName = dir()
Loop
End Sub

Just note that there are only 3 hundred columns in a worksheet, so 3 x 100 =
300 and you would run out of space.