How to produce two workbooks from a larger workbook

[LutonBarry]

Folks I hope this is simple. I have a workbook that has many sheets that I used to use to produce sa single report, using the first line "Sheets(Array......" instruction below.

I now want it to produce a second workbook containing different sheets from the original workbook. So I added the second line "Sheets(Array....) instruction but it doesn't like it. Is there a simple way round this for you clever folks.


""
Sheets(Array("Report","COpen", "CClosed", "CNew","WAll", "WOpen", "WClosed","WNew")).Copy
Sheets(Array("CImport", "WImport")).Copy
"

[p45cal]

Works here; what's the error message?

[LutonBarry]

Thanks for your reply p45cal. Think it was subscript out of range. But it was on my work PC so I'll check tomorrow and post an update. Your prompt has got me thinking, it's been a long day at work. When the first Sheets(Array...) instruction is run it creates the new workbook, I need to then activate the original workbook before running the second Sheets(Array...) instruction.

I'll check in the morning to see if that works it should do.

[p45cal]

Think it was subscript out of range.Then you've got one or more sheet names not an exact replica of the actual sheet name - check for spaces and the like.




When the first Sheets(Array...) instruction is run it creates the new workbook, I need to then activate the original workbook before running the second Sheets(Array...) instructionDidn't need to here, it depends in which workbook the code is in and perhaps where the code is in that workbook.

[snb]

Thisworkbook.Sheets(Array("Report","COpen", "CClosed", "CNew","WAll", "WOpen", "WClosed","WNew")).Copy
Thisworkbook.Sheets(Array("CImport", "WImport")).Copy

[LutonBarry]

p45call. Yes that was it when you create the copy of the sheets the new workbook is the activated one. I just needed to reactivate the original wherethe code was held and it worked.

Thanks for your help.

[snb]

Did you overlook http://www.vbaexpress.com/forum/showthread.php?50156-How-to-produce-two-workbooks-from-a-larger-workbook&p=312119&viewfull=1#post312119 ?

[LutonBarry]

snb, Ooops I did and thanks very much, less code and yet another learning from this great forum.

Thanks again.