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:
Could you please help me to modify this code in order to work even if the sheets are hidden?
Thanks in advance.
regards
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:
Code:
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