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 recieved 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 thi
makes a difference.



Thanks for your hel

 

[Tom Ogilvy]

Sub ABCD()
Dim i_TotalNumberOfColumns As Integer
i_TotalNumberOfColumns = 10
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


End Sub

ran fine for me with Reg being a worksheet or Reg being a chartsheet. Is
sheet Reg hidden?

 

[sakieboy]

Hey Tom,

I just ran your code and got the same error....
MS Excel 97, I don't think that it makes a difference...thanks for your
promptness. Not really sure what it's attempting to do, possibly
transport the data from a table to a graph (new code for me). Let me
know if you come up woth any more suggestions.

 

[sakieboy]

Hey Tom,



Any explanation to why mine still doesn't work...I tried you
code....and it gives me the same error

 

[sakieboy]

Hey Tom


I think I might have solved...would like your input on the solution.

'initial code...
' Sheets(Array(WorkSheet4_Reg_Data, Chart2_Reg)).Copy
'replace with....
Sheets(Array(WorkSheet4_Reg_Data, Chart2_Reg)).Select
Selection.Cop

 

[sakieboy]

I started Excel, Personal.xls
Created 2 Sheets, Sheet1 and Sheet2,
Created a macro, and out this in the macro
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate
Sheets(Array("Sheet1", "Sheet2")).Copy

I ran it and got the same error
run-time error '1004' Copy method of Sheets class failed

I then put a break point on the top line of the Macro and when it hi
the break point I manually selected the 2 Worksheets and placed th
following code in my immediate box and got the same error.

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


Thanks:confused