+ Reply to Thread
Results 1 to 5 of 5

Amazing disappearing error 1004

  1. #1
    Registered User
    Join Date
    04-04-2018
    Location
    Glasgow, Scotland
    MS-Off Ver
    MS 365
    Posts
    34

    Amazing disappearing error 1004

    I wrote some VBA to copy a range from a worksheet and paste it as values to another worksheet in the same workbook. It failed with error 1004. I set the macro recorder running and collected the code generated when I performed the operation by hand. As a stand-alone macro, it worked without error. When I appended these lines of code unaltered to my existing macro, error 1004 again appeared. After some trial and error, I found that if I segregated the copy/paste to a subroutine and called it with the source range as a parameter, it ran without error. So, summarising:

    Please Login or Register  to view this content.
    ....fails with error 1004. But:

    Test(r)

    ..runs without error, when Test is defined as:
    Please Login or Register  to view this content.

    What is going on?
    Last edited by DougieJLockhart; 04-04-2018 at 06:12 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Amazing disappearing error 1004

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I will do this for you on this occasion only.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Amazing disappearing error 1004

    You have not set r to a range which could be your problem

    Avoid lots of other problems by not selecting everything in sight!!
    Selecting is usually not required
    It slows everything down

    So if r is A1:A10
    Please Login or Register  to view this content.
    Selecting things
    If it is necessary to select sheets & cells.....
    ..for sheets I generally use Activate
    Please Login or Register  to view this content.
    Activate a sheet before trying to select any cells

    ...for cells use Select
    Please Login or Register  to view this content.
    If you want to select several sheets(to do the same thing to all of them) then use select (only one sheet can be active at the same time)
    Last edited by kev_; 04-04-2018 at 06:31 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  4. #4
    Registered User
    Join Date
    04-04-2018
    Location
    Glasgow, Scotland
    MS-Off Ver
    MS 365
    Posts
    34

    Re: Amazing disappearing error 1004

    r is set. I'm not that dumb!

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Amazing disappearing error 1004

    r is set. I'm not that dumb!
    Perhaps not
    How are we supposed to know that? We cannot read invisible code
    Free advice for you : Expect dumb answers if you provide incomplete information
    Last edited by kev_; 04-04-2018 at 07:54 AM.

+ 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. Your forums are amazing
    By Worsin in forum The Water Cooler
    Replies: 3
    Last Post: 04-11-2017, 05:04 AM
  2. Amazing Use of COUNTIFS ...
    By Vikas_Gautam in forum Tips and Tutorials
    Replies: 11
    Last Post: 08-14-2014, 02:33 AM
  3. [SOLVED] The amazing not disappearing box
    By malcmail in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-19-2014, 04:27 PM
  4. Been Here Before... Amazing how helpful
    By thinksnowjob in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-14-2013, 10:24 PM
  5. Need quick answer PIVOT/Transpose problem/solution
    By exceldba in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-19-2012, 02:03 PM
  6. Error "run-time Error '1004': General Odbc Error
    By D4WNO77 in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  7. Error 75 File/Path access error, sometimes Error 1004
    By smokebreak in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-16-2011, 02:35 PM

Tags for this Thread

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