Copy + Rename from Hidden Sheet

[rspalding]

Hello,

I'm using the code below to copy and rename worksheets. However i what the sheets that I copy from to be hidden. When I hide those sheets and try to copy i get an error. What code needs to be added?

Sub AutoAddSheet()
    Dim MyCell As Range, MyRange As Range
    Set MyRange = Sheets("Master").Range("B17")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))


    For Each MyCell In MyRange
        Sheets(MyCell.Value).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number
        Sheets(Sheets.Count).Name = MyCell.Offset(0, -1).Value 'Renames the new worksheets
    Next MyCell
    Worksheets("Master").Activate
End Sub [cpde]

Thanks for the help!

 

[Fluff]

Glad we could help & thanks for the feedback.

On your test file you had a hidden sheet called Form 5, which was causing the initial problems and may also be the reason that it appeared to be missing some sheets.

 

[mumps]

For what it's worth:

Sub AutoAddSheet()
    Application.ScreenUpdating = False
    Dim LastRow As Long, MyCell As Range
    LastRow = Sheets("Master").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each MyCell In Sheets("Master").Range("B17:B" & LastRow)
        If MyCell <> "" Then
            Sheets(MyCell.Value).Visible = True
            Sheets(MyCell.Value).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number
            Sheets(Sheets.Count).Name = MyCell.Offset(0, -1).Value 'Renames the new worksheets
            Sheets(MyCell.Value).Visible = False
        End If
    Next MyCell
    Worksheets("Master").Activate
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

@mumps
In the OPs file the last sheet was hidden, therefore even though you have

Sheets(MyCell.Value).Copy After:=Sheets(Sheets.Count)

It will actually put the new sheet as the penultimate sheet not the last sheet.

 

[mumps]

@Fluff: Thank you for pointing that out. I hadn't seen your Post #11 . I'll have to be a little more diligent in the future.