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!

 

[mumps]

Try:

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)
        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
    Next MyCell
    Worksheets("Master").Activate
    Application.ScreenUpdating = True
End Sub

 

[rspalding]

Hi,

Got a "Subscript out of range". It did copy the sheets but didn't rename.

Thanks....

 

[mumps]

Which line of code was highlighted when you clicked 'Debug'? Are there values in column A to be used as the sheet name?

 

[rspalding]

Yes there are values in column A. Does not give me the chance to Debug, just shows the error.

Thanks,

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[rspalding]

https://www.dropbox.com/s/m3cm5rdoik9hck4/LayOuts - Copy.xlsm?dl=0

 

[Fluff]

Try

Sub AutoAddSheet()
    Application.ScreenUpdating = False
    Dim LastRow As Long, MyCell As Range
    LastRow = Sheets("Master").Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each MyCell In Sheets("Master").Range("B17:B" & LastRow)
      If Not Evaluate("isref('" & MyCell.Offset(, -1).Value & "'!A1)") Then
        Sheets(MyCell.Value).Visible = True
        Sheets(MyCell.Value).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number
        ActiveSheet.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

 

[rspalding]

Hi Fluff,

Your code worked but only copied down 3 rows. It skipped the first row, B17 and then /stopped at B21.

Thanks,

 

[rspalding]

Fluff,

My bad. it works just fine. I must have done something wrong.

THANK YOU!!