Copying sheets

  • Thread starter Thread starter Greg H.
  • Start date Start date
G

Greg H.

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
 
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
 
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
 
Hi Greg

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

You try to copy a sheet that don't exist


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)




Greg H. said:
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
Click to expand...
 
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 said:
Hi Greg

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

You try to copy a sheet that don't exist


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*)
Click to expand...
 
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
 
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
 
Back
Top