HELP! what is wrong with my code :(

[ads_3131]

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]
_____________________________________________

[Aflatoon]

This is a long-standing issue with Excel. Usually if you save the workbook after a few iterations (20 for example) you can continue. Alternatively, you might try adding new sheets, then copying and pasting from your template sheet.

Edit: I am referring to this particular bug.

[Bob Phillips]

I just ran it for 44 and 3, no problems.

[ads_3131]

Hello thanks for the reply!

Ive tryed the saving when the counter gets to a certain number e.g 20 or 30 but it still does it!

And the insert new worksheet and copy doesnt work well because i dont get the exact format/same as the template

there must be a way........

[ads_3131]

xld

what version of excel are you using / vb? im running excell 2000 and visual basic 6 ...

reopen it and put 100 in.... thats the most anyone will want and what im hoping for....

tar

[Bob Phillips]

I am using Excel 2007 here. I just tried 100, worked fine.

Have you seen Aflatoon's post. I haven't seen this in action myself, but seeing it mentioned it does ring a bell, and may be the problem you are seeing.

[ads_3131]

yes, i agree xld.... ive tried it on my home excel 2007...

excel 2000 has many limitations and this looks to be with keeping in the application memory.....

problem is people here use 2000, and even putting a save on the counter when it reaches so many doesnt work.

i was really hoping there was some way of getting this problem bottomed.... :/

[Bob Phillips]

I say it again, have you seen Aflatoon's post?

EDit: sorry, just noticed you said the counter doesn't work. I have 2000 on another machine, I will see if I can try it later.