ExcelBanter - copy tabs into new workbook, save, and close.

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - copy tabs into new workbook, save, and close. (https://www.excelbanter.com/excel-programming/387735-copy-tabs-into-new-workbook-save-close.html)

copy tabs into new workbook, save, and close.

Good morning!

I'm trying to copy tabs from a current workbook, into a new file,
saving that file, closing it, then returning to the original file -
but my code seems to stop right after copying the tabs into a new
workbook. If someone could tell me what I'm doing wrong, that would
be really appreciated! I would love to have the user prompted to name
the file when they save it - but I'm not sure how to do it.

Sheets(Array("Tables", "Cashflows", "Calc Sheet", _
"Metrics", "REA", "Inputs")).Select
Sheets("Tables").Activate
Sheets(Array("Tables", "Cashflows", "Calc Sheet", _
"Metrics", "REA", "Inputs")).Copy
Sheets("REA").Range("E1").Select
** It ends right here **
Sheets(Array("Tables", "Calc Sheet")).Select
ActiveWorkbook.Save
ActiveWorkbook.Close savechanges:=True
Sheets("Menu").Select

Thank you!!

copy tabs into new workbook, save, and close.

On Apr 19, 9:28 am, Dave Peterson wrote:
You can only select a range if the sheet is active. So how about trying:

Sheets(Array("Tables", "Cashflows", "Calc Sheet", _
"Metrics", "REA", "Inputs")).Copy

Sheets("REA").select
sheets("Rea").Range("E1").Select

Sheets(Array("Tables", "Calc Sheet")).Select

'show the file|SaveAs dialog
application.dialogs(xldialogsaveAs).show

wrote:

Good morning!

I'm trying to copy tabs from a current workbook, into a new file,
saving that file, closing it, then returning to the original file -
but my code seems to stop right after copying the tabs into a new
workbook. If someone could tell me what I'm doing wrong, that would
be really appreciated! I would love to have the user prompted to name
the file when they save it - but I'm not sure how to do it.

Sheets(Array("Tables", "Cashflows", "Calc Sheet", _
"Metrics", "REA", "Inputs")).Select
Sheets("Tables").Activate
Sheets(Array("Tables", "Cashflows", "Calc Sheet", _
"Metrics", "REA", "Inputs")).Copy
Sheets("REA").Range("E1").Select
** It ends right here **
Sheets(Array("Tables", "Calc Sheet")).Select
ActiveWorkbook.Save
ActiveWorkbook.Close savechanges:=True
Sheets("Menu").Select

Thank you!!

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you sooooo much! It worked perfectly - I really appreciate it!!