Macro to Merge Sequential Numbered Sheets

[lesoies]

Using Excel 2010 and have a file with multiple worksheets and looking for a macro to combine the sheets where the sheet names have sequential numbering in parenthesis after the sheet name (with the exception of the first sheet). Example of the sheet names I wish to combine is as follows:

Claim Detail
Claim Detail(1)
Claim Detail(2)
Claim Detail(3)

The first sheet has the column headers, the rest of the sheets do not. There are other sheets in the workbook that do not need to be merged, just the ones that have the sequential numbering.

Also, different workbooks have a different number of columns for the sheets that need to be merged, so looking for one that looks for the "used range" to combine. Attached is an before and after example. The sheets typically goto 65535, since it was originally formmated for Excel 2003. Thanks.

[JOHN H. DAVIS]

Beware of your Sheet names, maybe:

Please Login or Register  to view this content.

[lesoies]

I am getting a "Run Time Error 9: Subscript out of Range" error. When I debug, the following line if highlighted:

Please Login or Register  to view this content.

If I add a period between "copy" and "sheets", I still get a "Complie Error Expected:=" at that line.

Regarding the sheet names, you are correct, they should all be labeled "Claim Detail" Attached are revised before and aftrer workbooks.

Also, there can be anywhere from 1 to 15 sheets in parenthesis (exported from Business objets). Can the macro be looped to inlcude all those, if present? Thanks again.

[JOHN H. DAVIS]

The runtime error was caused by the Sheet Names. Try this, it worked on your copy.

Please Login or Register  to view this content.

To modify for additional sheets 15 for example:

Change this line:

For i = 1 To 3


To this:

For i = 1 To 15

[lesoies]

It is kind of working, with a few problems as follows:

1. Column Headers are missing
2. Not all data is being merged properly (data is missing).

[JOHN H. DAVIS]

On your test files it matched your After sample when I ran it?

[lesoies]

Sorry about that, you are correct, it did worked. Not sure why I get that error. Will mark this as solved. Thanks for your help!!!

[JOHN H. DAVIS]

You're welcome. Glad too help out, and thanks for the feedback.