How to adapt the macro to allow it to consolidate files with multiple sheets?

**billj** · 08-04-2013, 12:06 AM

The macro below is able to combine multiple Excel files in a folder into a single workbook (each sheet of the new workbook represents the original individual Excel file). The little drawback is that the original Excel files is ONLY allowed to have ONE sheet for this macro to work. If a file has multiple sheets, then only one sheet is used for the consolidation.

Is there a way to modify it to allow it to accommodate multiple sheets of files to be consolidated?

Please Login or Register  to view this content.

**TMS** · 08-04-2013, 12:15 AM

Can't test it now but you'd define a variable "Dim sh As Worksheet" and then say:

Please Login or Register  to view this content.

Regards, TMS

**billj** · 08-04-2013, 12:36 AM

I adapted the macro as below, but still has a error which said:

Run-time error '1004': Application-defined or object-defined error

Please Login or Register  to view this content.

**billj** · 08-04-2013, 08:59 AM

Can you please help to take a look at the macro code?

It said 'Run-time error '1004': Application-defined or object-defined error'

**AB33** · 08-04-2013, 09:04 AM

In which line of the code do you get the error, so that it would be easy to identify it?

**HaHoBe** · 08-04-2013, 09:09 AM

Hi, billj,

wouldn´t it be simpler to copy the worksheets instead of adding a blank one in the master and then copy the contents across?

Maybe you should rethink the way to name a sheet to a file name if multiple sheets from that workbook are copied.

Ciao,
Holger

**billj** · 08-04-2013, 11:33 AM

Thanks for the advice. I attached the file with the macro included.

**billj** · 08-04-2013, 03:44 PM

Still waiting for an answer. Can anyone please help for the macro?

**HaHoBe** · 08-04-2013, 04:08 PM

Hi, billj,

Still waiting for an answer.

Well, if you want to copy multiple sheets from one workbook and assign the very same name to a worksheet that is already in the current workbook Excel VBA will raise a run-time Error. So you would need a different method than the one you used when only copying one sheet.

Please Login or Register  to view this content.

is good enough if there is one sheet which is given the name, for multiple sheets you should alter it to something like

Please Login or Register  to view this content.

Ciao,
Holger

**billj** · 08-04-2013, 05:18 PM

Hello HaHoBe, thanks a lot for the help. Your improved formula worked perfectly

How to adapt the macro to allow it to consolidate files with multiple sheets?

LinkBack

Thread Tools

Rate This Thread

Display

How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Re: How to adapt the macro to allow it to consolidate files with multiple sheets?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need help with a macro to consolidate data from multiple sheets into one

[SOLVED] Macro to read the multiple csv files and consolidate into single excel

[SOLVED] Need macro to consolidate data from select multiple sheets

Help! Macro to consolidate data from multiple workbook sheets!

Macro to oepn multiple txt files and then consolidate

Bookmarks

Bookmarks

Posting Permissions