Copying And Renaming Sheets XL2003

[Kevin H. Stecyk]

Hi,

XL 2003, Windows XP.

I have a question regarding copying and renaming sheets.

Sub Blah()

Dim oActiveSheet As Object

blah blah blah

Sheets("Duplicate").Copy Before:=Sheets("Duplicate")
'\ Set oActiveSheet = Sheets("Duplicate(2)").Select
Set oActiveSheet = ActiveSheet

End Sub

The commented line when uncommented does not work. I get a Run-time error
'9': Subscript out of range.

Yet the next line does work. Why doesn't the commented line work? What did
I do incorrectly? It doesn't seem to matter whether or not I have ".Select"
appended to the statement.

When I copy, there are no other copies of "Duplicate". VBA appears to name
the duplicate of "Duplicate" to "Duplicate (2)". So I am puzzled.

Thank you.

Best regards,
Kevin

 

[Guest]

this worked for me in xl2003

Sub copysheets()
Dim oActivesheet As Worksheet
Sheets("Duplicate") _
.Copy Before:=Sheets("Duplicate")
Set oActivesheet = Sheets("Duplicate (2)")

MsgBox oActivesheet.Name
End Sub

In think your problem is the select on the end.

 

[Kevin H. Stecyk]

Tom Ogilvy
....

this worked for me in xl2003

Sub copysheets()
Dim oActivesheet As Worksheet
Sheets("Duplicate") _
.Copy Before:=Sheets("Duplicate")
Set oActivesheet = Sheets("Duplicate (2)")

MsgBox oActivesheet.Name
End Sub

In think your problem is the select on the end.

Thank you Tom. I got it to work.

Best regards,
Kevin