1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy hidden sheets in new workbook

Discussion in 'VBA Macros' started by AlexSH09, Aug 15, 2018.

  1. AlexSH09

    AlexSH09 New Member

    Messages:
    7
    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:

    Code (vb):

    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
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,667
    Can you unhide them
    Copy them
    Rehide them
  3. AlexSH09

    AlexSH09 New Member

    Messages:
    7
    Yes, that would fit...
    Just don't know how to include this in the code above :(
  4. Stevie

    Stevie Active Member

    Messages:
    116
    Hi Alex, try replacing this bit of your code:

    Code (vb):
            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:
    Code (vb):
    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, Thomas Kuriakose and Hui like this.
  5. AlexSH09

    AlexSH09 New Member

    Messages:
    7
    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:

    Code (vb):
    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

Share This Page