Macro to consolidate 20 workbook sheets into one sheet.

[Alka Bajaj]

Hi Expert,

I have 20 excel files each having more that 1000 records, the format and header for all the files are same.

I need to consolidate all these 20 different files into one excel sheet, having only one header row at the top. Since I need to perform this step frequently, I am planning to automate it.

Hence please advice a macro for above.

Appreciate your guidance.

Thanks.
Regards,
Alka

 

[VoG]

This should help you http://msdn.microsoft.com/en-us/library/cc793964.aspx

 

[Glory]

How are they named? Sequentially?

 

[Alka Bajaj]

Currently they are named as following.
ACT report for PY area U0
ACT report for PY area A1
ACT report for PY area S0

It is not sequential. Basically it is named as per the country. However if it is required in macro we can follow some naming convention.

Thanks.

Regards,
Alka

 

[Glory]

No you can loop through all the files in a folder too. Check on that. If they're all in a single folder then it might be possible to use ADO to directly extract the data without even opening them, depending on the layout of the information on the sheets.

 

[RoryA]

Which version of Excel are you using?

 

[Alka Bajaj]

Hi Glory,

All the files kept in a single folder. What is ADO?

 

[Alka Bajaj]

Hi Rorya,

I am using Excel 2007 but my client is using 2003. So I would prefer 2003.

Thanks
Alka

 

[jeffreybrown]

This should help you http://msdn.microsoft.com/en-us/library/cc793964.aspx

Did you give any consideration or check-out what Peter posted?

 

[Alka Bajaj]

Hi Jeffrey,

I am looking into it now.