EXCEL VBA - Run-time Error 1004

Discussion in 'Microsoft Excel Charting' started by sakieboy, Jan 29, 2004.

  1. sakieboy

    sakieboy Guest

    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
     
    sakieboy, Jan 29, 2004
    #1
    1. Advertisements

  2. sakieboy

    Jon Peltier Guest

    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
    >
    >
     
    Jon Peltier, Jan 29, 2004
    #2
    1. Advertisements

  3. sakieboy

    sakieboy Guest

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

    >
    >.
    >
     
    sakieboy, Jan 29, 2004
    #3
  4. sakieboy

    Jon Peltier Guest

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

    >>
    >>.
    >>
     
    Jon Peltier, Jan 30, 2004
    #4
  5. sakieboy

    sakieboy Guest

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

    >
    >.
    >
     
    sakieboy, Jan 30, 2004
    #5
  6. sakieboy

    Jon Peltier Guest

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

    >>
    >>.
    >>
     
    Jon Peltier, Jan 30, 2004
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SteveBanks

    vba create multiple series xl3dline chart - error 1004

    SteveBanks, Nov 12, 2003, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    746
    Jon Peltier
    Nov 13, 2003
  2. TJonesEsq

    Run-Time Error '1004'

    TJonesEsq, Oct 28, 2004, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    773
    Jon Peltier
    Oct 29, 2004
  3. TJonesEsq

    Run-Time Error '1004'

    TJonesEsq, Oct 29, 2004, in forum: Microsoft Excel Charting
    Replies:
    0
    Views:
    189
    TJonesEsq
    Oct 29, 2004
  4. Mau

    excel chart export error runtime 1004

    Mau, Aug 16, 2005, in forum: Microsoft Excel Charting
    Replies:
    3
    Views:
    573
  5. Ajay_N
    Replies:
    1
    Views:
    282
    Jon Peltier
    Aug 15, 2006
Loading...

Share This Page