Basically i have the below code running on a button....
it works fine if the user puts 1 in, 5 in and so on but after it copying/pasting the template sheet 44 times it throws a error up/even if you run it 44 times! and wont work anymore....
the only way currently to bypass it is completely close down excel and re-open
attached is a example... to start go to the summary tab and click the button, input how many sheets you want....
any help would be great im banging my head against the wall!
tar
code below:
________________________________________
[VBA]Sub Bttn_Multiple_Click()
Dim Sh As Worksheet, TemplateSh As Worksheet
Dim ShNum As Integer, HighestNum As Integer
Dim SheetCoreName As String
Dim counter As Long
'Call up a user input box to determine how many times this code is to be looped
HowManyTimes = InputBox("How Many SCAF Forms Do You Want to Create?", "New SCAFF")
If HowManyTimes = "" Then Exit Sub
For counter = 1 To HowManyTimes
' INDICATE THE CORE SHEET NAME
SheetCoreName = "SCAF"
' INDICATE THE SOURCE SHEET
Set TemplateSh = Sheets("Template")
' DETERMINE NEXT NUMBER FOR SHEET
For Each Sh In Worksheets
If InStr(1, Sh.Name, SheetCoreName) = 1 Then
ShNum = Val(Right(Sh.Name, Len(Sh.Name) - Len(SheetCoreName)))
If ShNum > HighestNum Then HighestNum = ShNum
End If
Next Sh
' COPY TEMPLATE
TemplateSh.Copy after:=Sheets(Sheets.Count)
' MAKE VISIBLE
ActiveSheet.Visible = xlSheetVisible
' RENAME
ActiveSheet.Name = SheetCoreName & HighestNum + 1
Next
End Sub[/VBA]
_____________________________________________