Consolidating Several Excel Files

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


*** Sent via Developersdex http://www.developersdex.com ***

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.

--
Regards,
Tom Ogilvy


"Darin Kramer" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

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



*** Sent via Developersdex http://www.developersdex.com ***

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


*** Sent via Developersdex http://www.developersdex.com ***

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

*** Sent via Developersdex http://www.developersdex.com ***

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.

--
Regards,
Tom Ogilvy


"Darin Kramer" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

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.

--
Regards,
Tom Ogilvy

"Darin Kramer" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

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.

--
Regards,
Tom Ogilvy



"Darin Kramer" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***