+ Reply to Thread
Results 1 to 10 of 10

Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Grass Lake, Michigan
    MS-Off Ver
    Excel 2007
    Posts
    124

    Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    I have a macro button on one worksheet (call it Sheet1), which (as part of its process) tries to copy (and paste) a range of cells on another worksheet (call it OtherSheet). The macro uses row and column numbers to define the copy range. I started with a statement like this:

    Please Login or Register  to view this content.
    That gave me a runtime Error 1004. So I tried this:

    Please Login or Register  to view this content.
    This gives the same Error 1004 (on the line with the Set command). I accidentally discovered, however, that if I run the macro from within the VBA editor, while OtherSheet (not Sheet1) is the active sheet, that it runs OK. If Sheet1 is the active sheet, it fails.

    So what's going on? I'm specifically referencing the worksheet to copy from (OtherSheet). Why does it care which sheet is active? How can I get it to work when OtherSheet is not the active sheet?

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    The Cells property is a Range type and should also be worksheet specific.

    Please Login or Register  to view this content.
    Try that and see if it fixes it. Also, what do you have defined as CopyRow

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    Or

    Please Login or Register  to view this content.
    See http://www.excelforum.com/excel-prog...ml#post4092238
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,223

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    Without a sheet reference Cells will refer to the active sheet.
    If posting code please use code tags, see here.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    How is Set CopyRange defined? The variable has to be an object, Like Set CopyRange as range

  6. #6
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Grass Lake, Michigan
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    Thanks, all, for the amazingly quick help!

    I included only the line(s) I was having an issue with. CopyRow was previously declared as Integer and was set equal to the row number I wished to copy from (in this particular instance, 15). Likewise, CopyRange had been previously declared as Range.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    CopyRow was previously declared as Integer
    It should be declared as Long. There are more rows in Excel than Integer can handle.

  8. #8
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,298

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    Or this maybe.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Grass Lake, Michigan
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    Quote Originally Posted by shg View Post
    It should be declared as Long. There are more rows in Excel than Integer can handle.
    Good point! I will update that as well.

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Range(Cells(a,b),Cells(c,d)) Only Works if Sheet is Active?? (Error 1004)

    You're welcome, good luck.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error 1004 when selecting a range of cells
    By isrisian in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-18-2011, 07:01 PM
  2. range(cells(),cells()) from 2 different workbooks, run time error 1004
    By sam0287 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2010, 05:46 AM
  3. Error 1004: Pastespecial method of range class failed, macro works sometimes!
    By sam0287 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-19-2009, 09:12 PM
  4. Worksheet.Range(Cells(r,c),Cells(r,c)) & error 1004
    By onebir in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-28-2007, 05:10 AM
  5. Charting - Cells and Range - Error 1004
    By vbaprog in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2005, 04:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1