ads_3131
08-22-2011, 04:36 AM
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:
________________________________________
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
_____________________________________________
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:
________________________________________
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
_____________________________________________