Copying sheets
 

I am trying to create a number of copies of my existing sheet through a
for...next loop. Here is the code that I am trying to use.

Public Sub Copies30()

Dim I As Integer
Dim Sheetname As String

For I = 1 To 30
Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)
Next I

End Sub

When stepping through this, I am getting a "subscript out of range error".
I can't understand why, as if I add a watch on Worksheets("PC 1"), I am able
to get a reference to the worksheet.

Is there anyone who can spot the problem?

Thanks in advance,

Greg

Copying sheets
 

Try this one Greg

Public Sub Copies30()
Dim I As Integer
Application.ScreenUpdating = False
For I = 1 To 30
Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I
Next I
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg H." wrote in message ...
I am trying to create a number of copies of my existing sheet through a
for...next loop. Here is the code that I am trying to use.

Public Sub Copies30()

Dim I As Integer
Dim Sheetname As String

For I = 1 To 30
Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)
Next I

End Sub

When stepping through this, I am getting a "subscript out of range error".
I can't understand why, as if I add a watch on Worksheets("PC 1"), I am able
to get a reference to the worksheet.

Is there anyone who can spot the problem?

Thanks in advance,

Greg

Copying sheets
 

Thank you,

your code executed perfectly and took care of my immediate need. Just for
my future knowledge, do you know why I was getting the subscript out of
range error? I'm still not sure where the error was in my code. I would
like to know so that I can avoid this in the future.

Thanks again,
Greg

"Ron de Bruin" wrote in message
...
Try this one Greg

Public Sub Copies30()
Dim I As Integer
Application.ScreenUpdating = False
For I = 1 To 30
Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I
Next I
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl

Copying sheets
 

Hi Greg

Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)

You try to copy a sheet that don't exist


Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I

You see my code copy the Sheet "PC" after the last sheet in the workbook (Worksheets(Sheets.Count)
Then give it a name PC & I (the number in the loop)
And copy the sheet "PC" again and give it a name(30*)

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg H." wrote in message ...
Thank you,

your code executed perfectly and took care of my immediate need. Just for
my future knowledge, do you know why I was getting the subscript out of
range error? I'm still not sure where the error was in my code. I would
like to know so that I can avoid this in the future.

Thanks again,
Greg

"Ron de Bruin" wrote in message
...
Try this one Greg

Public Sub Copies30()
Dim I As Integer
Application.ScreenUpdating = False
For I = 1 To 30
Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I
Next I
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl

Copying sheets
 

I'm sorry Ron,

I wasn't clear in my first post, my first sheet was named "PC 1". So in this
code...

Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)

on the first iteration of the loop should evaluate to...

This Workbook.Worksheets("PC 1").Copy after:= Worksheets("PC 1")

Shouldn't it? The second line of the 2 line code segment is where I was
getting the error.

I apologize for taking more of your time and energy now that I have the
solution, but I always prefer the understanding so that I don't ask the same
question twice.

Thanks,
Greg



"Ron de Bruin" wrote in message
...
Hi Greg

Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)

You try to copy a sheet that don't exist


Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I

You see my code copy the Sheet "PC" after the last sheet in the workbook
(Worksheets(Sheets.Count)
Then give it a name PC & I (the number in the loop)
And copy the sheet "PC" again and give it a name(30*)

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg H." wrote in message
...
Thank you,

your code executed perfectly and took care of my immediate need. Just
for
my future knowledge, do you know why I was getting the subscript out of
range error? I'm still not sure where the error was in my code. I would
like to know so that I can avoid this in the future.

Thanks again,
Greg

"Ron de Bruin" wrote in message
...
Try this one Greg

Public Sub Copies30()
Dim I As Integer
Application.ScreenUpdating = False
For I = 1 To 30
Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I
Next I
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl

Copying sheets
 

Hi Greg

My mistake

Your code will work when I = 1
because "PC 1" exist, so it can copy the sheet.

But the second time it want to copy "PC 2" and this sheet not exist
because the name = "PC 1 (2)"

This will work when I name the sheet
ActiveSheet.Name = "PC " & I + 1


Public Sub Copies30()
Dim I As Integer
Dim Sheetname As String
For I = 1 To 30
Sheetname = "PC " & I
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)
ActiveSheet.Name = "PC " & I + 1
Next I
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg H." wrote in message ...
I'm sorry Ron,

I wasn't clear in my first post, my first sheet was named "PC 1". So in this
code...

Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)

on the first iteration of the loop should evaluate to...

This Workbook.Worksheets("PC 1").Copy after:= Worksheets("PC 1")

Shouldn't it? The second line of the 2 line code segment is where I was
getting the error.

I apologize for taking more of your time and energy now that I have the
solution, but I always prefer the understanding so that I don't ask the same
question twice.

Thanks,
Greg



"Ron de Bruin" wrote in message
...
Hi Greg

Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)

You try to copy a sheet that don't exist


Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I

You see my code copy the Sheet "PC" after the last sheet in the workbook
(Worksheets(Sheets.Count)
Then give it a name PC & I (the number in the loop)
And copy the sheet "PC" again and give it a name(30*)

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg H." wrote in message
...
Thank you,

your code executed perfectly and took care of my immediate need. Just
for
my future knowledge, do you know why I was getting the subscript out of
range error? I'm still not sure where the error was in my code. I would
like to know so that I can avoid this in the future.

Thanks again,
Greg

"Ron de Bruin" wrote in message
...
Try this one Greg

Public Sub Copies30()
Dim I As Integer
Application.ScreenUpdating = False
For I = 1 To 30
Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I
Next I
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl

Copying sheets
 

I forgot this

You use this
Sheetname = "PC " & Str(I)

The str will add a space so your name is PC 1(with two spaces)

You can use this
Sheetname = "PC" & Str(I)
I remove the space after PC


I use this in the code
Sheetname = "PC " & I


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Greg

My mistake

Your code will work when I = 1
because "PC 1" exist, so it can copy the sheet.

But the second time it want to copy "PC 2" and this sheet not exist
because the name = "PC 1 (2)"

This will work when I name the sheet
ActiveSheet.Name = "PC " & I + 1


Public Sub Copies30()
Dim I As Integer
Dim Sheetname As String
For I = 1 To 30
Sheetname = "PC " & I
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)
ActiveSheet.Name = "PC " & I + 1
Next I
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg H." wrote in message ...
I'm sorry Ron,

I wasn't clear in my first post, my first sheet was named "PC 1". So in this
code...

Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)

on the first iteration of the loop should evaluate to...

This Workbook.Worksheets("PC 1").Copy after:= Worksheets("PC 1")

Shouldn't it? The second line of the 2 line code segment is where I was
getting the error.

I apologize for taking more of your time and energy now that I have the
solution, but I always prefer the understanding so that I don't ask the same
question twice.

Thanks,
Greg



"Ron de Bruin" wrote in message
...
Hi Greg

Sheetname = "PC " & Str(I)
ThisWorkbook.Worksheets(Sheetname).Copy after:=Worksheets(Sheetname)

You try to copy a sheet that don't exist


Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I

You see my code copy the Sheet "PC" after the last sheet in the workbook
(Worksheets(Sheets.Count)
Then give it a name PC & I (the number in the loop)
And copy the sheet "PC" again and give it a name(30*)

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg H." wrote in message
...
Thank you,

your code executed perfectly and took care of my immediate need. Just
for
my future knowledge, do you know why I was getting the subscript out of
range error? I'm still not sure where the error was in my code. I would
like to know so that I can avoid this in the future.

Thanks again,
Greg

"Ron de Bruin" wrote in message
...
Try this one Greg

Public Sub Copies30()
Dim I As Integer
Application.ScreenUpdating = False
For I = 1 To 30
Worksheets("PC").Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = "PC " & I
Next I
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl