Copy all the sheet from closed workbook except hidden sheets

Question

I want to copy all the sheet from specific workbook to current workbook after Sheet1

Sub CopyAllSheets()
Dim MastWB  As Workbook
Dim SalesWB As Workbook


    Set MastWB = ThisWorkbook 'change to suit
    Set SalesWB = Workbooks.Open("F:\WIN7PROFILE\Desktop\Rporting\Test.xls")

    SalesWB.Sheets.Copy after:=MastWB.Sheets("Sheet1")
    Workbook.SalesWB.Close

End Sub

This code is copying all the sheets, including hidden ones. How to copy only the visible sheets?

SalesWB.Sheets.Copy after:=MastWB.Sheets("Sheet1") Run time error '1004' Can not rename sheet to the same name as another sheet, a referenced object library or a workbook reference by visual basic — khyati dedhia
I have also tried different codes but getting same errorSub CopyAllSheets2() Dim b1 As Workbook, b2 As Workbook Dim sh As Worksheet Set b1 = ThisWorkbook Workbooks.Open Filename:=("F:\WIN7PROFILE\Desktop\Rporting\test.xls") Set b2 = ActiveWorkbook For Each sh In b2.Sheets sh.Copy after:=b1.Sheets(b1.Sheets.Count) Next sh End Sub — khyati dedhia
Apologies, while running the code, excel is also coping hidden worksheet as well which is causing the problem. Do you know code which help me to not to copy hidden sheets. — khyati dedhia

Cindy Meister Cindy Meister · Accepted Answer · 2019-07-22T10:02:06

The code will need to loop the Worksheets in the source workbook and check the Visible property. This can be any member of the XlSheetVisibility enumeration: xlSheetHidden, xlSheetVeryHidden or xlSheetVisible. If you want only the sheets that are visible, then something like the code that follows (that bases on the code in the question).

Note that you may want to change ThisWorkbook to ActiveWorkbook as ThisWorkbook means the workbook that contains the code...

Sub CopyAllSheets()
Dim MastWB  As Workbook
Dim SalesWB As Workbook
Dim ws as Worksheet

    Set MastWB = ThisWorkbook 'change to suit
    Set SalesWB = Workbooks.Open("F:\WIN7PROFILE\Desktop\Rporting\Test.xls")

    For Each ws In MastWB.Worksheets
        If ws.Visible = xlSheetVisible Then
          SalesWB.Sheets.Copy after:=MastWB.Sheets(ws)
          Workbook.SalesWB.Close
        End If
    Next
End Sub

Copy all the sheet from closed workbook except hidden sheets

1 Answers