PC Review


Reply
Thread Tools Rate Thread

EXCEL VBA - Run-time Error 1004

 
 
sakieboy
Guest
Posts: n/a
 
      29th Jan 2004
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 With Quote
 
 
 
 
Jon Peltier
Guest
Posts: n/a
 
      29th Jan 2004
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/
_______

sakieboy wrote:

> 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 With Quote
 
 
 
 
sakieboy
Guest
Posts: n/a
 
      29th Jan 2004
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:
>
>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/
>_______
>
>sakieboy wrote:
>
>> 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 With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      30th Jan 2004
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/
_______

sakieboy wrote:
> 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:
>>
>>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/
>>_______
>>
>>sakieboy wrote:
>>
>>
>>>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 With Quote
 
sakieboy
Guest
Posts: n/a
 
      30th Jan 2004
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
>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/
>_______
>
>sakieboy wrote:
>> 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:
>>>
>>>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/
>>>_______
>>>
>>>sakieboy wrote:
>>>
>>>
>>>>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 With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      30th Jan 2004
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/
_______

sakieboy wrote:
> 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
>
>>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/
>>_______
>>
>>sakieboy wrote:
>>
>>>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:
>>>>
>>>>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/
>>>>_______
>>>>
>>>>sakieboy wrote:
>>>>
>>>>
>>>>
>>>>>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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
run time error 1004 general odbc error excel 2003 vba =?Utf-8?B?TWVudG9z?= Microsoft Excel Programming 5 24th Jan 2011 03:56 PM
Excel VBA error 1004: General ODBC Error rhysgowat Windows Vista 0 24th Jul 2010 02:20 PM
Error 1004 opening workbook 1004 WembleyBear Microsoft Excel Programming 2 30th Nov 2009 02:33 PM
Excel VBA - Runtime Error "1004" General ODBC Error SKS Microsoft Excel New Users 2 23rd Jun 2004 07:54 PM
runtime error 1004 when opening excel file via VBA in a browser Ken Hunter Microsoft Excel Programming 2 31st Jul 2003 03:33 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:27 PM.