Copy worksheet multiple times then rename then copy and paste information that's different

I'm trying to copy a worksheet ("blank") multiple times based on the Inputs sheet. Each row of the input sheet represents the information to copy and paste to the newly copied sheets. I have attempted this in two different ways to accomplish.

Here is code #1

Sub AddSheets()
Dim codes As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Sheets("Inputs").Select
For Each i In Range("Codes")
On Error GoTo ErrMsg
Range("b6:cp6").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.name = i
n = ActiveSheet.name
Sheets("Blank").Select
Cells.Select
Selection.Copy
Sheets(n).Select
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll
Sheets("Inputs").Select
Selection.Offset(1, 0).Activate
Selection.Copy
Sheets(n).Select
Range("b3").Select
ActiveSheet.Paste Link:=True
ActiveSheet.Range("b3").PasteSpecial Paste:=xlPasteFormats
ActiveWindow.DisplayGridlines = False
Next i
Sheets("Inputs").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
ErrMsg:
MsgBox ("Delete Old Sheets Before Creating New.")
End Sub


The only issue I have with code #1 is that it takes a really long time to process. I need this to create over 500+ sheets and most of the time ends up not responding. So I searched for another way.

Here is code #2


Function CheckSheetExists(ByVal name As String)
Dim retVal As Boolean
retVal = False
For S = 1 To Sheets.Count
If Sheets(S).name = name Then
retVal = True
Exit For
End If
Next S
CheckSheetExists = retVal
End Function

Sub AutoAddSheet()
Dim MyCell As Range, MyRange As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set MyRange = Sheets("Inputs").Range("B7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
If CheckSheetExists(MyCell.Value) = False Then
Sheets("Blank").Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.name = MyCell.Value
.Cells(2, 1) = MyCell.Value 'can this be removed
End With
End If
Next MyCell
Sheets("Inputs").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub

Option #2 actually now has 2 different problems, (1) is that I still need to copy the input schedule data and (2) as the sheet counts get higher it sometimes doesn't respond trying to add any additional sheets.

Any thoughts on which route would be best? Also, how can I fix the problems that I'm facing.

Thanks for the reply Mumps1. I have attached a sample file.
Ultimately, I'm trying to accomplish the following:

1) Create a new sheet (using “blank” as template) for each item on the “inputs” sheet (name of sheet is column b).
2) Copy the information from “input” sheet related to the newly created sheet and paste link and formatting onto that newly created sheet on row 3.
3) Create a hyperlink on “input” sheet to the newly created sheet.

I first created Option 1, but realized that this is a slow vba code method. Since it requires the usage of activesheet, select, etc to accomplish the end goal.

Option 1 (Add button) – I’m only including here, since it actually does do all the steps necessary to reach end goal. However, in the full model this button hangs the model (stops responding) when trying to add 100+ pages. This led me to try and find a better way ... and arrived at Option 2 method.

Option 2 (Copy button) - This method is faster to create, and even allows me to add additional sheets. This also hangs (stops responding) when trying to add 100+ even when I'm adding 25 at a time on the full model. The model will ultimately be 500+ pages being created. I still haven’t been able to get it to copy the additional “inputs” information. I’ve tried numerous ways with no solution yet.

Here's the areas I need the assistence:
1) For option 2, how would I copy and paste the link onto each newly created sheet?
2) How would I be able to get this to not hang when trying to add 100+ or even 500+ at a time.

FYI – the other 2 buttons are used to facilitate adding rows to the inputs page and deleting the pages being created.

Additionally, I'm still working on the Blank sheets calculations to see if there is some further shrinking that can be done ... which should help with the speed as well.

Any help that anyone can provide would be greatly appreciated.

If you have an xlsb file with just 2 sheets and hardly any data in it and it is close to 300 KB, can you imagine what size it'll be with 500 sheets added.
Ctrl+End on the "Blank" sheet puts you at Row 1174 and Column 143. Why?
IMHO, it would be easier and faster to add 500 sheets, add the formulas (is that what you mean by links?) and format a range if required. Hopefully not 143 columns by 1174 rows as you have it now.
I like this the best.
3) Create a hyperlink on “input” sheet to the newly created sheet.
You want 500 hyperlinks. Easy to work with!

Thanks for the response. I appreciate you taking the time to help. I have tested the macro, the only issue that I’m having with the new macro is that it is copying the formula’s from the inputs schedule, which is causing them to show incorrectly (i.e., “2” through “11” now show exactly the same information when they shouldn’t).

This macro has been the fastest to execute. I however, still have the hanging problem. I have gone through the file and removed all the unused columns and rows, I even tried to remove the hyperlink component and still doesn’t work, the hyperlinks only added a couple kb to size. After the comment from jolivanes, I looked into the file sizes of each sheet, every new sheet adds about 800kb ... adds up fast.

Thanks for the help again. My next step is to look into breaking into separate workbooks (individually or multiple at a time) the new sheet creation and if sum 3D works across workbooks.

Let me know if you have any thoughts. Hopefully, I’m not going down another pathway that doesn’t work, even if I have a lot of manual work to do.

Thanks again.