Saving Multiple Sheets to Another Spreadsheet Without Leaving the Initial Workbook
 

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

Saving Multiple Sheets to Another Spreadsheet Without Leaving the Initial Workbook
 

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

--
Regards,
Tom Ogilvy

"Carroll" wrote in message
ups.com...
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

Saving Multiple Sheets to Another Spreadsheet Without Leaving the Initial Workbook
 

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

Saving Multiple Sheets to Another Spreadsheet Without Leaving the Initial Workbook
 

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","Sheet 4")).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

--
Regards,
Tom Ogilvy


"Carroll" wrote in message
ups.com...
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

Saving Multiple Sheets to Another Spreadsheet Without Leaving the Initial Workbook
 

Thanks Tom. That fixed everything.

Carroll