+ Reply to Thread
Results 1 to 13 of 13

Run-Time error 1004

  1. #1
    Registered User
    Join Date
    01-20-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Run-Time error 1004

    I am very new to vba and I'm trying to write a marco to sort out data. However, when it finishes with calculating the previous, it kept on showing me the runtime error. Also, I am sure there is a way to loop this instead of me just simply copy and past the command.

    What it does is to change range A2 to S7, S8, S9... then calculate the sheet and copy and paste the data into a different sheet renamed after what is specified in cell A2. This process then go back to the lookup sheet and repeat the same for s8 etc...

    Can someone give me some advices please?? Thanks!!

    See below for the code:

    Please Login or Register  to view this content.
    Last edited by yz604; 01-20-2014 at 05:08 AM.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Run-Time error 1004

    Welcome to the forum.

    We'd like to help you but first..

    Pls take some minutes to read forum rules and specially-in this case- rule#3
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Run-Time error 1004

    Hi,
    i have a solution for you, but before i can post it you need to do the following.

    at the bottom of your post, select edit post, then select the code in your post and then click the # symbol on the tool bar.
    then save it. this will place your code in a different format that is easier to read and complies with the rules.

    then i can help you
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  4. #4
    Registered User
    Join Date
    01-20-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Run-Time error 1004

    HI Sean, Thanks for the advice, I've resaved it in the format that is required. Please help me to have a look. Thanks.

  5. #5
    Registered User
    Join Date
    01-20-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Run-Time error 1004

    Hi, I have modified as per requested. I hope it's okay now. Thanks.

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Run-Time error 1004

    Quote Originally Posted by yz604 View Post
    Hi, I have modified as per requested. I hope it's okay now. Thanks.
    Yes. Thank you.

  7. #7
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Run-Time error 1004

    Hi, had to go out for a while.

    This should sort your problem.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-20-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Run-Time error 1004

    Thanks, however, I still experience the runtime error with the code you modified.

    I need to select A2 and change it to for example S1, then calculate sheet. Once the calculation finishes, I will need to copy all the date to a standalone tab called S1, but paste special (numbers) only, then go back to the previous sheet (called lookup), then change A2 to S2...

    Can you help me please?

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Run-Time error 1004

    That procedure does what you want except copy values only.
    After changing A2 the sheet will recalculate unless you have turned calculation to manual.

    you should not need:
    Please Login or Register  to view this content.
    Run time error 1004 can be caused if any one of the cells you are copying has over 911 characters in it. do you?

    My code ran perfectly ok but i didnt have any data in the range being copied. Try it in a blank workbook.

    Do you have any merged cells in that range?
    Can you provide a sample workbook with the typical data that you are calculating/copying as this might be causing the error.

  10. #10
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Run-Time error 1004

    If you just want to copy values then quickest method is:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-20-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Run-Time error 1004

    I have turned it off the calculation. I.e. I've turned it into manual.

    What I'd like is to change A2 then calculate sheet, then copy data to a different tab. The data is less than 911 characters I think

  12. #12
    Registered User
    Join Date
    01-20-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Run-Time error 1004

    would you be able to help me to see how I can make this calculation process more automatic please? Thanks!

  13. #13
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Run-Time error 1004

    you need to have your calculation set to automatic.
    Then as soon as the code changes A2 it will recalculate the sheet before it copies the data.

    I cant help anymore without viewing your workbook.

+ 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. Replies: 4
    Last Post: 11-15-2013, 05:03 PM
  2. VBA Code...error = run time error 1004 autofilter method of range class failed
    By Dariusd7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2013, 04:49 PM
  3. 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
  4. Excel Macro Error - Run time error 1004 - Paste method of worksheet class failed
    By kvflynn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 10:51 AM
  5. [SOLVED] run-time error '1004': Application-defined or object-deifined error
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2005, 05:05 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