Hi all

i think this is a glitch or maybe its a clipboard error i dont know but i
have a macro that creates worksheets based on a worksheet already in the pack
but when it reaches 24 copies created it stops and throws up an error. i
tried to create one manually but it does not allow me to create a new one
then. i close down the pack and re open it and the macro runs again with
another 24 sheets. am i missing something?

heres the macro


Sub updatevalues()
Dim sel, i, sheetnu As Integer
Dim check As Boolean

check = False
sel = Sheets("content").Range("a2")
sheetnu = 15
i = 3

Do Until check = True
If sel = 0 Then
check = True
Else
Sheets("2").Copy Befo=Sheets(sheetnu)
sheetnu = sheetnu + 1
ActiveSheet.Name = i
ActiveSheet.Range("B4") = i
ActiveSheet.Calculate
i = i + 1
sel = sel - 1
End If
Loop

End Sub

thank you

Why are you using an intermediate variable in your Do Loop, when you
could just check the dependent variable directly?

sel = Sheets("content").Range("a2")
Do Until sel = 0
Sheets("2").Copy Befo=Sheets(sheetnu)
sheetnu = sheetnu + 1
ActiveSheet.Name = i
ActiveSheet.Range("B4") = i
ActiveSheet.Calculate
i = i + 1
sel = sel - 1
Loop


And what is the value in Sheets("content").Range("a2") when you first
start the code?

Also, you are declaring variables as Variant type unintentionally:

Dim sel, i, sheetnu As Integer

is transformed by VBA to

Dim sel As Variant
Dim i As Variant
Dim sheetnu As Integer

You can share the word "Dim", but not a declaration. Use "Dim sel As
Integer, i As Integer, sheetnu As Integer" instead.


--JP


On Sep 18, 2:56*pm, Rivers wrote:
Hi all

i think this is a glitch or maybe its a clipboard error i dont know but i
have a macro that creates worksheets based on a worksheet already in the pack
but when it reaches 24 copies created it stops and throws up an error. i
tried to create one manually but it does not allow me to create a new one
then. i close down the pack and re open it and the macro runs again with
another 24 sheets. am i missing something?

heres the macro

Sub updatevalues()
Dim sel, i, sheetnu As Integer
Dim check As Boolean

check = False
sel = Sheets("content").Range("a2")
sheetnu = 15
i = 3

Do Until check = True
* * * * If sel = 0 Then
* * * * * * check = True
* * * * Else
* * * * * * Sheets("2").Copy Befo=Sheets(sheetnu)
* * * * * * sheetnu = sheetnu + 1
* * * * * * ActiveSheet.Name = i
* * * * * * ActiveSheet.Range("B4") = i
* * * * * * ActiveSheet.Calculate
* * * * * * i = i + 1
* * * * * * sel = sel - 1
* * * * End If
Loop

End Sub

thank you

its actually a bug which i found in http://support.microsoft.com/?kbid=210684

basically it causes an error for copying anything over 20 programmatically

if i strip out the variables and just put it basic does anyone no a work
around

Sheets("2").Select
Do Until check = True
If sel = 0 Then
check = True
Else
Sheets("2").Copy After:=Sheets(sheetnu)
sheetnu = sheetnu + 1
ActiveSheet.Name = ii
ActiveSheet.Calculate
sel = sel - 1
i = i + 1
End If
Loop

"JP" wrote:

Why are you using an intermediate variable in your Do Loop, when you
could just check the dependent variable directly?

sel = Sheets("content").Range("a2")
Do Until sel = 0
Sheets("2").Copy Befo=Sheets(sheetnu)
sheetnu = sheetnu + 1
ActiveSheet.Name = i
ActiveSheet.Range("B4") = i
ActiveSheet.Calculate
i = i + 1
sel = sel - 1
Loop


And what is the value in Sheets("content").Range("a2") when you first
start the code?

Also, you are declaring variables as Variant type unintentionally:

Dim sel, i, sheetnu As Integer

is transformed by VBA to

Dim sel As Variant
Dim i As Variant
Dim sheetnu As Integer

You can share the word "Dim", but not a declaration. Use "Dim sel As
Integer, i As Integer, sheetnu As Integer" instead.


--JP


On Sep 18, 2:56 pm, Rivers wrote:
Hi all

i think this is a glitch or maybe its a clipboard error i dont know but i
have a macro that creates worksheets based on a worksheet already in the pack
but when it reaches 24 copies created it stops and throws up an error. i
tried to create one manually but it does not allow me to create a new one
then. i close down the pack and re open it and the macro runs again with
another 24 sheets. am i missing something?

heres the macro

Sub updatevalues()
Dim sel, i, sheetnu As Integer
Dim check As Boolean

check = False
sel = Sheets("content").Range("a2")
sheetnu = 15
i = 3

Do Until check = True
If sel = 0 Then
check = True
Else
Sheets("2").Copy Befo=Sheets(sheetnu)
sheetnu = sheetnu + 1
ActiveSheet.Name = i
ActiveSheet.Range("B4") = i
ActiveSheet.Calculate
i = i + 1
sel = sel - 1
End If
Loop

End Sub

thank you