The following code is part of a macro I have set up. The first two lines
(select "CR" and rename as "EQUIP-CR") work perfectly, but I get a run-time
error 9 (Subscript out of range) when it gets to the copy worksheet line.

Sheets("CR").Select
ActiveSheet.Name = "EQUIP-" & ActiveSheet.Name

Sheets("EQUIP-CR").Copy After:=ThisWorkbook.Sheets(Sheets.Count)

I'm sure it is something very simple, but my VBA skills are limited and I
haven't been able to figure this one out. Any help is appreciated.

I copy and pasted it and it worked perfectly, where did you put this code at?
Should be in ThisWorkbook or a module.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"ksh" wrote:

The following code is part of a macro I have set up. The first two lines
(select "CR" and rename as "EQUIP-CR") work perfectly, but I get a run-time
error 9 (Subscript out of range) when it gets to the copy worksheet line.

Sheets("CR").Select
ActiveSheet.Name = "EQUIP-" & ActiveSheet.Name

Sheets("EQUIP-CR").Copy After:=ThisWorkbook.Sheets(Sheets.Count)

I'm sure it is something very simple, but my VBA skills are limited and I
haven't been able to figure this one out. Any help is appreciated.

The code is in the destination workbook. It seems to work okay on previous
entries in the macro. The code is rather long (again still learning) but it
basically opens various workbooks and copies data to the destination workbook
holding the code. I have added more of the code below (the full piece that
relates to the equip workbook).

'Opens the workbook for the month being reported
Workbooks.Open _
("Q:\LATIN AMERICA\SABRE\2007\DATA " + sPeriod + "\BCDLA EQUIP " + sPeriod +
".XLS")

'Check to see if there is a worksheet for this country
On Error Resume Next
Set WS = Worksheets("CR")
On Error GoTo 0

If WS Is Nothing Then
'closes the last workbook opened without saving changes
Workbooks(Workbooks.Count).Close (False)
Else
'rename country worksheet with data type description
Sheets("CR").Select
ActiveSheet.Name = "EQUIP-" & ActiveSheet.Name

Sheets("EQUIP-CR").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
'closes the last workbook opened without saving changes
Workbooks(Workbooks.Count).Close (False)

End If


"John Bundy" wrote:

I copy and pasted it and it worked perfectly, where did you put this code at?
Should be in ThisWorkbook or a module.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"ksh" wrote:

The following code is part of a macro I have set up. The first two lines
(select "CR" and rename as "EQUIP-CR") work perfectly, but I get a run-time
error 9 (Subscript out of range) when it gets to the copy worksheet line.

Sheets("CR").Select
ActiveSheet.Name = "EQUIP-" & ActiveSheet.Name

Sheets("EQUIP-CR").Copy After:=ThisWorkbook.Sheets(Sheets.Count)

I'm sure it is something very simple, but my VBA skills are limited and I
haven't been able to figure this one out. Any help is appreciated.

Sheets.count is referening to the active workbook and not thisworkbook. Give
this tweak a try...

with ThisWorkbook
Sheets("EQUIP-CR").Copy After:=.Sheets(.Sheets.Count)
end with

--
HTH...

Jim Thomlinson


"ksh" wrote:

The following code is part of a macro I have set up. The first two lines
(select "CR" and rename as "EQUIP-CR") work perfectly, but I get a run-time
error 9 (Subscript out of range) when it gets to the copy worksheet line.

Sheets("CR").Select
ActiveSheet.Name = "EQUIP-" & ActiveSheet.Name

Sheets("EQUIP-CR").Copy After:=ThisWorkbook.Sheets(Sheets.Count)

I'm sure it is something very simple, but my VBA skills are limited and I
haven't been able to figure this one out. Any help is appreciated.

Thank you. It worked perfectly. I would never have been able to do this
without this group as a resource.

"Jim Thomlinson" wrote:

Sheets.count is referening to the active workbook and not thisworkbook. Give
this tweak a try...

with ThisWorkbook
Sheets("EQUIP-CR").Copy After:=.Sheets(.Sheets.Count)
end with

--
HTH...

Jim Thomlinson


"ksh" wrote:

The following code is part of a macro I have set up. The first two lines
(select "CR" and rename as "EQUIP-CR") work perfectly, but I get a run-time
error 9 (Subscript out of range) when it gets to the copy worksheet line.

Sheets("CR").Select
ActiveSheet.Name = "EQUIP-" & ActiveSheet.Name

Sheets("EQUIP-CR").Copy After:=ThisWorkbook.Sheets(Sheets.Count)

I'm sure it is something very simple, but my VBA skills are limited and I
haven't been able to figure this one out. Any help is appreciated.