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 Befo=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

this worked for me in xl2003

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

MsgBox oActivesheet.Name
End Sub

In think your problem is the select on the end.

--
Regards,
Tom Ogilvy


"Kevin H. Stecyk" wrote:

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 Befo=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

Tom Ogilvy
....
this worked for me in xl2003

Sub copysheets()
Dim oActivesheet As Worksheet
Sheets("Duplicate") _
.Copy Befo=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