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 Before:=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 <Riv...@discussions.microsoft.com> 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 Before:=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
>
>
Similar Threads
