Auto-Create Tabs VBA crashes

[johnsending]

Hello everyone,

I'm encounter an issue with Excel when using the formula below. I have a button with the VBA code on it and it works perfectly but if my list consists of more than 15 entries, Excel crashes when trying to auto-create the tabs after the 15th entry. Is there any way to modify the code to only do 15 at a time (1st - 15 entries, then next set of 15 entries, then the next 15...) when pressed repeatedly? I'm also open to other corrections since I can only press the button once before getting an error. I'm not well-versed on VBA.

Sub CreateDeviceSheets()
Dim wsNew As Worksheet
Dim strName As String
Dim I As Long

With Sheets("Switch-List")

For I = 2 To .Range("A" & Rows.Count).End(xlUp).Row
strName = .Range("A" & I).Value
Set wsNew = Sheets(.Range("B" & I).Value)
With wsNew
.Visible = xlSheetVisible
.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = strName
.Visible = xlSheetHidden
End With
ActiveSheet.Name = strName
Next I

End With

End Sub



Thanks,

John

 

[jmacleary]

Hi there. I've just tried this code and it ran fine for me creating 33 sheets. I noticed that you have to have an existing sheet name in column B for it to work - is it possible your 16th row has an invalid sheet name? Another source of error would be if the 16th. entry in column A is an already existing sheet name.

 

[johnsending]

Not sure what changed but tried it again today and it seems to be working fine now. I went all the way up to 40 with no problem. With that said, is there anyway to add to the VBA code a way to Hyperlink the name of on the column to the sheet it auto-created?

 

[My Aswer Is This]

If you would like a link to all your sheets.
Here is a script that will enter all your sheet names In Column A of a sheet named Master.
Then attach a link to that sheet.

So in column A of sheet named Master you will have a list of all your sheet names.

Now if your sheet is name George and you click on George you will now be taken to sheet named George

And on each sheet there will be put a link in Range("A1") that will take you back to sheet named Master

Just change Master to what ever sheet you want.

Sub AddHyperLinks()
'Modified 4-20-18 8:20 AM EST
Dim C As Range
Dim i As Long
Dim ans As String
ans = "Master" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    For i = 2 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(ans).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next

With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub

 

[johnsending]

Thank you for the hyperlink VBA Code! I will see how I can incorporate it to my existing VBA code for the Auto-creation sheets. I mentioned on my first post about how the code could be executed only once. Once the tabs are created, if I have the wrong template for the tab come up, I would have to delete all my tabs and then go back and click on the button to execute the code once more without getting an error. Is there a way to modify the code I posted (copied again below) to allow certain "checked" columns to kick a new tab instead of the entire column?

Thanks again.

 

[Fluff]

How about

Sub CreateDeviceSheets()
   Dim wsNew As Worksheet
   Dim strName As String
   Dim i As Long
   
   Application.ScreenUpdating = False
   With ("Switch-List")
      For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
         strName = .Range("A" & i).Value
         If Not Evaluate("isref('" & strName & "'!a1)") Then
            Set wsNew = Sheets(.Range("B" & i).Value)
            With wsNew
               .Visible = xlSheetVisible
               .Copy After:=Sheets(Sheets.Count)
               ActiveSheet.Name = strName
               .Visible = xlSheetHidden
            End With
            .Cells(i, 1).Hyperlinks.Add Anchor:=.Cells(i, 1), Address:="", SubAddress:="'" & strName & "'!A1"
         End If
      Next i
   End With
End Sub

If the sheet already exists it will ignore it.

 

[johnsending]

YES! Thanks everyone (especially Fluff for the finishing touch) for providing answers and suggestions. I only needed to add the word "Sheets" in the With ("Switch-List") and I worked perfectly. Problem solved!

 

[Fluff]

Glad we could help & thanks for the feedback