VB code to copy hidden sheets not working properly

[rspalding]

I'm using the code below. If I only have one hidden sheet it works fine. but i have multiple hidden sheets abd at that point it no longer functions properly.

Please help. thank you,

Sub AutoAddSheet()
    Application.ScreenUpdating = False
    Dim LastRow As Long, MyCell As Range
    LastRow = Sheets("Info Entry").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each MyCell In Sheets("Info Entry").Range("N26:N" & 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, -13).Value 'Renames the new worksheets
        Sheets(MyCell.Value).Visible = False
    Next MyCell
    Worksheets("Info Entry").Activate
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

As you haven't told us what is "not working", this is a guess.

Sub AutoAddSheet()
   Application.ScreenUpdating = False
   Dim LastRow As Long, MyCell As Range
   LastRow = Sheets("Info Entry").Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   For Each MyCell In Sheets("Info Entry").Range("N26:N" & LastRow)
      Sheets(MyCell.Value).Visible = True
      Sheets(MyCell.Value).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number
      [COLOR=#ff0000]ActiveSheet.Name[/COLOR] = MyCell.Offset(0, -13).Value 'Renames the new worksheets
      Sheets(MyCell.Value).Visible = False
   Next MyCell
   Worksheets("Info Entry").Activate
   Application.ScreenUpdating = True
End Sub

 

[rspalding]

Hi Fluff,

I tried the correction that you sent. i was trying to copy 3 sheets, 2 the same and one different. It copied all 3 sheets (all correct copies). But the 3rd sheet was hidden, the first sheet was left un-hidden, it made a copy of the first sheet with (2) in the name. So

Thank you,

 

[Fluff]

Did you get any error messages?
And do any of the new sheet names already exist in the workbook?

 

[rspalding]

Hi,

No error message other than "Subscript out of range". And no names already exist.

Thanks,

 

[Fluff]

Which line of code gives that error?

 

[rspalding]

Hi,

No error message other than "Script out of range". No sheet names already exist.

Thanks,

 

[rspalding]

it's not like a regular code error where i can step into it. I say OK and then it copies the sheets but only if 1 hidden sheet.

 

[Fluff]

Subscript out of range suggest that a sheet name does not exist.
Check that the values in col N are exactly the same as the sheet names.

 

[rspalding]

Hi Fluff,

The problem appears to be the multiple hidden sheets. I will eventually have about 20 hidden sheets and copying up to about 30 sheets.

Thanks,