Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying sheets | Excel Worksheet Functions | |||
Copying Sheets | Excel Worksheet Functions | |||
Copying Sheets | Excel Discussion (Misc queries) | |||
copying sheets | Excel Discussion (Misc queries) | |||
Copying sheets | Excel Programming |