Saving Multiple Sheets to Another Spreadsheet Without Leaving the Initial Workbook

[Carroll]

Hello,

I have a workbook that has 7 sheets. I need to create 3 spreadsheets
out of this: 4 sheets to one spreadsheet, 2 to another and 1 to a
third.

Using VBA, is there a way to do this without leaving the original
spreadsheet?

Thanks,

Carroll Rinehart

 

[Tom Ogilvy]

worksheets(Array("Sheet1","Sheet2","Sheet3","Sheet4")).copy
set bk1 = ActiveWorkbook
thisworkbook.Activate
worksheets(Array("Sheet5","Sheet6")).copy
set bk2 = ActiveWorkbook
thisworkbook.Activate
worksheets("Sheet7").copy
set bk3 = ActiveWorkbook
thisworkbook.Activate

 

[Carroll]

Tom,

This worked nicely. I now have Book2, Book3, and Book4 that are in
memory along with the original spreadsheet. I was also going to
automate it so that each new spreadsheet will be saved with a specific
name in a separate folder, along with the date embedded in the
spreadsheet names. I know how to save spreadsheets with the date
embedded in the name, but I'm not sure how I am going to handle this,
where I don't know beforehand what temporary name is being assigned to
each extracted spreadsheet? Is it doable?

Carroll

 

[Tom Ogilvy]

That is why I set references to each workbook created

Dim bk1 as Workbook, bk2 as Workbook, bk3 as Workbook
worksheets(Array("Sheet1","Sheet2","Sheet3","Sheet4")).copy
set bk1 = ActiveWorkbook
thisworkbook.Activate
worksheets(Array("Sheet5","Sheet6")).copy
set bk2 = ActiveWorkbook
thisworkbook.Activate
worksheets("Sheet7").copy
set bk3 = ActiveWorkbook
thisworkbook.Activate

bk1.SaveAs Filename:="whatever1"
bk2.SaveAs Filename:="Whatever2"
bk3.SaveAs Filename:="Whatever3"
bk1.close SaveChanges:=False ' already been saved
bk2.close SaveChanges:=False
bk3.close SaveChanges:=False

 

[Carroll]

Thanks Tom. That fixed everything.

Carroll