Automatically generate new sheets to existing work log

[mbeltran752]

I have a work log that is running out of sheets. I need to generate new sheets with the existing template. I also have a table at the beginning of the workbook that hyperlinks to the numbered sheet. I'm sure there is a faster way to accomplish these tasks. Please help!

 

[My Aswer Is This]

What does this mean?
"I have a work log that is running out of sheets"

Do you mean a Workbook that is running out of sheets?
Or what is a "work log"

Excel has no limit on number of sheets unless you run out of Memory.

 

[mbeltran752]

The work log is a workbook that stores a report page. I want to add more sheets to this workbook with the template copied to each new sheet. I know how to add new sheets and how to copy the template onto a new sheet. But I need to generate about 500 more sheets with hyperlinks to them from the main sheet. I want to know if there's a faster way to add more sheets and have them automatically named, i.e 501, 502, 503.

 

[My Aswer Is This]

We would normally not make a new sheet and then copy the template to the new sheet.

We would tell the script to copy the Template.

Why would this not work for you.

And explain how you want them named.

Not sure what and where your HYperlinks are.

If you template sheet name is "Template" your script would look like this:

Try this and see if this does what you want and we can then modify this to meet your exact needs
This script will copy the "Template" sheet 3 times.

Sub Copy_Template()
Dim i As Long
Application.ScreenUpdating = False
    For i = 1 To 3
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = i
    Next
Application.ScreenUpdating = True
End Sub

 

[mbeltran752]

Awesome! I really appreciate your help.

The sheets are numerical. Currently at 496. So the next sheets will be 497, 498, etc.

The hyperlinks are at the beginning of the workbook in table. I took a screen capture to give you a better idea.

 

[My Aswer Is This]

This script will allow you to select how many new sheets and choose the starting sheet number.

You need to explain more about the links.
Are the links only on sheet named "Report Log"
And are the links in column "A" starting in row (1)

And where on the sheets does the link take you.
And how did you create these links?
Some links will take you to sheet ?? Range ??

Sub Copy_Template()
Dim i As Long
Application.ScreenUpdating = False
Dim ans As String
Dim num As String
num = InputBox("How Many New Sheets")
ans = InputBox("New Sheet Starting Number")
    For i = 1 To num
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = ans
        ans = ans + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Assuming you want all your links entered into Sheet named "Report Log"
In column "A" starting with row (2) use this script below.
The script will clear column "A" first and then add in new links.
Run this script after you have added your new sheets.

Sub AddHyperLinks()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Report Log").Cells(Rows.Count, "A").End(xlUp).Row

Dim C As Range
Sheets("Report Log").Range("A2:A" & Lastrow).ClearContents
    For b = 3 To Sheets.Count
        Sheets("Report Log").Range("A" & b - 1).Value = Sheets(b).Name
    Next
With Sheets("Report Log")
    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