PDA

View Full Version : [SOLVED] How to produce two workbooks from a larger workbook



LutonBarry
07-15-2014, 09:52 AM
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
07-15-2014, 11:12 AM
Works here; what's the error message?

LutonBarry
07-15-2014, 11:58 AM
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
07-15-2014, 12:08 PM
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
07-15-2014, 01:39 PM
Thisworkbook.Sheets(Array("Report","COpen", "CClosed", "CNew","WAll", "WOpen", "WClosed","WNew")).Copy
Thisworkbook.Sheets(Array("CImport", "WImport")).Copy

LutonBarry
07-16-2014, 01:33 AM
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
07-16-2014, 04:46 AM
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
07-16-2014, 05:45 AM
snb, Ooops I did and thanks very much, less code and yet another learning from this great forum.

Thanks again.