Merging specific sheets

[WasWodge]

HI there, Does anyone now the correct syntax to alter the Ron de Bruin code below so it only merges specific sheets. So in my particular case I have sheets named "TEAM 1", "TEAM 2", "TEAM 3" etc, so for example I would like to merge "TEAM 3", "TEAM 5" and "TEAM 7" only.
I believe that only the line

Please Login or Register  to view this content.

needs changing but I have posted the full code (Including the functions) in case there is something else that needs changing.

Please Login or Register  to view this content.

[snb]

Do not overcomplicate things: this suffices

Please Login or Register  to view this content.

And if you want to retain the formulas & formatting:

Please Login or Register  to view this content.

[WasWodge]

Snb, Apologises if I have done something wrong but I can't get the code to copy the sheets across. It does create the summary sheet.

I have tried amending your line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Which also didn't copy any of the specific sheets
and

Please Login or Register  to view this content.

Which copied all the sheets to the summary sheet.

Can you give me any further advise?

[amarendra19]

Hi,

My Macros throws error "Type Mismatch" while running.The reason is, some of cell values in my sheet contains "#DIV/0!".This is due to error calculation in formula.When we divide by 0 (zero) or a blank cell, Microsoft Excel displays the error value "#DIV/0!" as the result of the calculation.So its throwing “ Type Mismatch” error.

So my decalred variable (sVal) reads this value as “ : sval : Error 2007 : Variant/Error.
Also some cell contains N/A.

Please suggest me how can i handle these values. So that the macro can run without any interupption. I am facing this issue while running the macros written for "Duplicate Data" calculation in a worksheet.

[WasWodge]

amarendra, you need to put your question in a new thread and not in another threads reply.

snb, repeating my query below

Snb, Apologises if I have done something wrong but I can't get the code to copy the sheets across. It does create the summary sheet.

I have tried amending your line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Which also didn't copy any of the specific sheets
and

Please Login or Register  to view this content.

Then
Which copied all the sheets to the summary sheet.

Can you give me any further advise?

[snb]

check the function lcase in vbeditor's help (F1).
read the explanation of Instr in vbeditor's help.
check the sheetnames (no spaces after the name e.g.)

Please Login or Register  to view this content.

[WasWodge]

Cheers snb, the last code posted is working as requested. Thanks for all your help.

[WasWodge]

Sorry to be a real pain and having to take the "Solved " off but is there a way to add more than 1 summary sheet with different "TEAMS". When I have tried repeating the code above either in the same sub or in a separate sub it seems to merge together with the previous summary.
If it is possible can you show me how the would look for a second summary sheet using team 11 and 12.
It seems that I can't take the string past 9 in that correct or is there a way around it as after further trying it seems to work up to team 9 but if i go hiher than that I think it picks up sheet 1 from the first digit above 10.

[WasWodge]

For info it does appear that the I can't go above team 9 because of the string. I have got around this by adding some code to change the tab names above team 9 by adding a letter i.e. team b11 but I would still like to know if there was a better way of achieving this end.
I will mark this as solved after allowing a bit of time to see if anyone has a better solution.