Trying to Copy sheets to a new workbook then hide sheets on original workbook

Matt Miller
Matt Miller used Ask the Experts™
on
I'm trying to copy a group of sheets to a new workbook and then hide the initial sheets on the original workbook?'

      Sheets(Array("Read Me", "Sheet1", "Sheet2")). _
        Select

    Sheets(Array("Read Me", "Sheet1", "Sheet2")). _
        Copy
'Hide Readme

Open in new window


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011

Commented:
To copy then hide all the originals, you could use:

    With Worksheets(Array("Read Me", "Sheet1", "Sheet2"))
        .Copy
        .Visible = xlSheetHidden
    End With

Open in new window

Rob HensonFinance Analyst

Commented:
After copying to a new workbook, the new sheets will be active.

Therefore you will have to re-activate the source workbook to hide the original sheets.
Most Valuable Expert 2011
Top Expert 2011

Commented:
@Rob
Not if you hold a reference to them, either with a With block, or a variable.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

Commented:
@Rory - didn't know that, everyday's a school day.
Rob HensonFinance Analyst

Commented:
Otherwise, I would have suggested:

Source = ActiveWorkbook.Name
Sheets(Array("Read Me", "Sheet1", "Sheet2")).Select
Sheets(Array("Read Me", "Sheet1", "Sheet2")).Copy
Windows(Source).Activate
Sheets(Array("Read Me", "Sheet1", "Sheet2")).Visible = xlSheetHidden

Open in new window

Group Finance Manager
Commented:
Assuming the code is in the original workbook, this workbook can be referred to as ThisWorkBook. When the sheets are copied then the new workbook created will be the ActiveWorkBook

Dim NewWb As String

ThisWorkbook.Sheets(Array("Read Me", "Sheet1", "Sheet2")).Copy
ThisWorkbook.Sheets(Array("Read Me", "Sheet1", "Sheet2")).Visible = xlSheetHidden

''/// save the new workbook
NewWb = Application.GetSaveAsFilename

If NewWb <> False Then
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & NewWb, FileFormat:=xlWorkbookNormal
End If

Open in new window

Author

Commented:
Thank you very much.  This is exactly what I needed.
Roy CoxGroup Finance Manager

Commented:
Pleased to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial