auto copying Excel spreadsheets
 

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

auto copying Excel spreadsheets
 

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

auto copying Excel spreadsheets
 

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