Can I copy a sheet to a new workbook if it's hidden

Question

So maybe I'm just not getting something here, but I have a hidden worksheet that list several columns based on other hidden sheets. I'm trying to minimize user intervention... :-)

I want to copy Hidden sheet 1 into a brand new workbook as an available sheet with the aforementioned values. Code that works when hiddensheet is visible:

Dim wbNew As Workbook

     Application.DisplayAlerts = False
    Worksheets("HiddenSheet").Copy

    Set wbNew = ActiveWorkbook
    With wbNew
        With .Worksheets(1).UsedRange
            .Value = .Value
        End With
        .SaveAs ThisWorkbook.Path & "\"
        .Close True
     End With

So I'd like to still copy the sheet to a new workbook....just want to do it with the sheet hidden.

Any thoughts?

Turn off screenupdating, unhide sheet, copy the sheet to new workbook, re-hide sheet, turn on screenupdating. — Chrismas007

Dirk Reichel Dirk Reichel · Accepted Answer · 2015-11-02T20:42:49

is is impossible to copy a hidden sheet to a new workbook
the new workbook would only have 1 sheet (hidden), but every wb needs at least one visible sheet

if you create a visible sheet and copy your hidden sheed later into it, it works

Sub Makro1()
  Dim wbNew As Workbook
  Set wbNew = Workbooks.Add
  ThisWorkbook.Sheets("Hidden Sheet").Copy Before:=wbNew.Sheets(1)
End Sub

try it :)

Can I copy a sheet to a new workbook if it's hidden

2 Answers