Copy hidden sheets in new workbook

[AlexSH09]

Hi all,

I'm working on a macro to copy some sheets of a workbook in a new one.
My problem is when the sheets are hidden, it doesn't work and show the ErrCatcher message:

Option Explicit
Sub CButton1_Click()
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet
    If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
    "New sheets will be pasted as values" _
    , vbYesNo, "NewCopy") = vbNo Then Exit Sub

    With Application
        .ScreenUpdating = False
        On Error GoTo ErrCatcher
        Sheets(Array("Detail 1", "Detail 2", "Detail 3", "Detail 4", "Detail 5")).Copy
        On Error GoTo 0

        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select
        NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xls"
        ActiveWorkbook.Close SaveChanges:=False
        .ScreenUpdating = True
    End With
  
    Application.DisplayAlerts = False
    Workbooks.Open Filename:= _
        ThisWorkbook.Path & "\" & NewName & ".xls"
    ActiveWorkbook.SaveAs Filename:= _
       ThisWorkbook.Path & "\" & NewName & ".xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWindow.Close
    Exit Sub

ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub

Could you please help me to modify this code in order to work even if the sheets are hidden?

Thanks in advance.

regards

 

[Hui]

Can you unhide them
Copy them
Rehide them

 

[AlexSH09]

Yes, that would fit...
Just don't know how to include this in the code above

 

[Stevie]

Hi Alex, try replacing this bit of your code:

        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws

with this:

Dim wasHidden As Boolean
For Each ws In ActiveWorkbook.Worksheets
    If ws.Visible = False Then
        wasHidden = True
        ws.Visible = True
    End If
    ws.Cells.Copy
    ws.[A1].PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Cells(1, 1).Select
    ws.Activate
    If wasHidden Then
        ws.Visible = False
        wasHidden = False
    End If
Next ws

---
It seems unlikely to me that the person who wrote this code would not know how to do what I have illustrated above, as it is well indented and uses some code shortening techniques not usually employed by a beginner.
If you did not write this code, have you thought of asking the person who did to make this change for you?

If this was helpful, please click 'Like'!
Stevie

 

[AlexSH09]

Hi thank you for your answer.

I just tried to replace the the code and it doesn't work.
The macro goes to the ErrCatcher at this step:

On Error GoTo ErrCatcher
    Sheets(Array("Detail 1", "Detail 2", "Detail 3", "Detail 4", "Detail 5")).Copy
    On Error GoTo 0

Maybe here too we need to unhide first?

Thanks for your help