EXCEL VBA - Run-time Error 1004

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sakieboy - 29 Jan 2004 14:29 GMT

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

Reply to this Message

Jon Peltier - 29 Jan 2004 17:37 GMT

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

> run-time error 1004 - Copy method of sheets class failed.
>
[quoted text clipped - 19 lines]
>
> Thanks for your help

Reply to this Message

sakieboy - 29 Jan 2004 18:15 GMT

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
****************************************

>-----Original Message-----
>Worked for me. So did this, which is a little smoother:
[quoted text clipped - 40 lines]
>
>.

Reply to this Message

Jon Peltier - 30 Jan 2004 13:24 GMT

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

> I tried your code and got subscript out of range. I
> replaced it with different, could you check to make sure
[quoted text clipped - 61 lines]
>>
>>.

Reply to this Message

sakieboy - 30 Jan 2004 15:39 GMT

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.

>-----Original Message-----
>Both ways worked. Your way worked right away without any problem; I'm
[quoted text clipped - 76 lines]
>
>.

Reply to this Message

Jon Peltier - 30 Jan 2004 18:55 GMT

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

> Well at least I thought it worked.....
>
[quoted text clipped - 107 lines]
>>
>>.

Reply to this Message