EXCEL VBA - Run-time Error 1004

[sakieboy]

run-time error 1004 - Copy method of sheets class failed.

Sheets("All_Reg_Data").Select
Dim i_WHY_AM_I_5 As Integer
i_WHY_AM_I_5 = 5
Range(Cells(1, i_WHY_AM_I_5), Cells(1,
i_TotalNumberOfColumns - 1)).Select
Selection.Copy
Sheets("Reg_Data").Select
Cells(1, i_WHY_AM_I_5).Select
ActiveSheet.Paste
Sheets(Array("Reg_Data", "Reg")).Select
Sheets("Reg").Activate
Sheets(Array("Reg_Data", "Reg")).Copy

I received the error on the final line.

The table consists of 6 sheets.

The Reg_data is a table of Data, the Reg is a graph. Not
sure if this makes a difference.



Thanks for your help

 

[Jon Peltier]

Worked for me. So did this, which is a little smoother:

Dim i_WHY_AM_I_5 As Integer
Dim i_TotalNumberOfColumns As Integer
i_WHY_AM_I_5 = 5
i_TotalNumberOfColumns = 9
Sheets("All_Reg_Data").Range(Cells(1, i_WHY_AM_I_5), _
Cells(1, i_TotalNumberOfColumns - 1)).Copy _
Sheets("Reg_Data").Cells(1, i_WHY_AM_I_5)
Sheets(Array("Reg_Data", "Reg")).Copy

- Jon

 

[sakieboy]

I tried your code and got subscript out of range. I
replaced it with different, could you check to make sure
that my result will be the same. Or, will something that
I've overlooked happen. Thanks.



Replaced:
****************************************
Sheets(Array("Reg_Data", "Reg")).Copy
****************************************

With
****************************************
Sheets(Array("Reg_Data", "Reg")).Select
Selection.Copy
****************************************

 

[Jon Peltier]

Both ways worked. Your way worked right away without any problem; I'm
not sure why you had the problem. The one-line version I showed is
generally preferred, since it's inefficient to select objects, then
perform actions on the selection.

- Jon

 

[sakieboy]

Well at least I thought it worked.....

Here's the code...That I put in the Personal.XLS

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate
Sheets(Array("Sheet1", "Sheet2")).Copy

I have 2 sheets, Sheet1 and Sheet2 (or is that Thing1 and
Thing2) anyway...

I get the same error. If I put a break point on the first
line and manually select the 2 sheets, and place the
code "Sheets(Array("Sheet1", "Sheet2")).Copy" in the
immediate window, I get the same error.

 

[Jon Peltier]

Oh, if the code is in personal.xls, you probably have to fully reference
the sheets:

With ActiveWorkbook
.Sheets(Array("Sheet1", "Sheet2")).Select
.Sheets("Sheet1").Activate
.Sheets(Array("Sheet1", "Sheet2")).Copy
End With

or

With ActiveWorkbook
.Sheets(Array("Sheet1", "Sheet2")).Copy
End With

- Jon