Run-time Error Every Other Time The Macro Runs

  • I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time.

    Every time I get the error, it highlights this line of code:

    Sheets("Regenerate Request").Paste

    This is all of the code up to where I get the error:

    Can someone help me?? Thanks in advance!!!

  • Re: Run-time Error Every Other Time The Macro Runs

    To stop the unprotecting/protecting of sheets for macros to work see: Run Macros On Protected Sheets

    Also, wouldn't your ActiveCell differ each time?

  • Re: Run-time Error Every Other Time The Macro Runs

    Hi Andy---
    I have actually put a line of code in that runs a seperate macro to unprotect the sheet after it is selected and before any cells are selected. The code for the RegenFormUnprotect macro that is called is

    Sub RegenFormUnprotect()
        Password = "ReGen"
        ActiveSheet.Unprotect Password
    End Sub

    Just to make sure this is not the error, I think I will unlock all the sheets and try the code, and see if that is it or not.

    Hi Dave---
    Thanks for the protecting/unprotecting info. I will try the UserInterfaceOnly method because that looks like its alot more effecient than what I'm currently using.

    Yes, you are correct that the Active Cell will change each time. The user selects a cell in column A of the "Request Log" worksheet (the macro attached to the button on that sheet won't run unless they select a cell in column A). Since I have been having this problem with the run-time error, I have tried both using the same cell over and over, and trying different cells each time, and either way the macro only runs correctly every other time.

    A co-worker of mine who knows a little about VBA suggested there might be something in the body of the macro that might be causing the problem. Would it be helpful if I posted the entire code? (I didn't before so as to keep my post short and concise)

  • Re: Run-time Error Every Other Time The Macro Runs

    Please post the entire code.

    One way of avoiding the error might be to lose the activate/select parts of the code.

    It's not generally needed to activate/select workbooks/ranges to work with them.

  • Re: Run-time Error Every Other Time The Macro Runs

    Hi Norie---
    Here is the entire code. It is actually two macros; the first macro is attached to the button on the log page, and if the user hits Yes when the MsgBox pops up, the second macro is called. The purpose of this is to pull data out of out log and put it back onto the request form to send out for a bid on pricing. If the user hits No, the macro quits and the user is left on the log worksheet.

    Hi Andy---
    I am trying to get a sample file I can post...I just keep getting pulled away from it, and for some reason none of my passwords are working anymore so I have to figure that out first!


  • Re: Run-time Error Every Other Time The Macro Runs

    Hi Andy---
    I finally got a sample workbook set up, but it is too big to post even when I have it zipped. Can I email it to you?

  • Re: Run-time Error Every Other Time The Macro Runs

    omg its the attack of the evil excel macro recorder...
    Have you tried just using the copy to destination feature?

    Sub YesRegen() 
         ' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
         ' the log, generates the new file and attaches it to an email
        Application.Run "LogUnprotect" 
        Application.Run "RegenFormUnprotect"
        Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy Sheets("Regenerate Request").Range("A40")

    the more you select/activate things, the more the macro and efficiency slows down. you should never have to actually select anything. just call on it and execute your command.

    Norie has mentioned this above. Try wouldn't believe how much of that code (i say about 75%) is useless.

  • Re: Run-time Error Every Other Time The Macro Runs

    Hi Norie---
    The active sheet when the RegenerateRequest macro is run is the Cost Request Log. The code copies over the cells in the ActiveCell.Row (the user selects the row) on the Log over to A40 on the Regenerate Request Form. When I get the error, the Regenerate Request Form is the active sheet.

    Hi gmccreedy---
    I'm still pretty new to VBA and am 100% self-taught through the help menu, what I've found here on OzGrid and information elsewhere on the web. I had seen the destination feature mentioned in one post, but couldn't figure out how to make it work with my code. I will try your suggestion and see if I can get it. I appreciate your advice to get rid of many of the selection and activation commands, and I did try to avoid those when I was writing this code, but I actually had to add some of those commands to get the code to where it is now. Is there anything specifically you would suggest that I take out, and if so, can you help me with the correct syntax to make the code run correctly? I've been struggling with this for awhile!!!

  • Re: Run-time Error Every Other Time The Macro Runs

    will get back to you in a bit. Will trump it down for you...but I am sure these guys will trim it even further. Like you, I am still a newbie...but I can assure you are in the right place for help!!!

    Back in a bit.

  • Re: Run-time Error Every Other Time The Macro Runs

    Ok. I did a little damage to it. Tough to edit some of the code since I wasn't quite sure what it was doing. (you had a few areas where you pasted twice, so wasn't sure if it was a mistake and did the best I could to decipher.)

    This is untested. I suggest not overwriting your original code and just set this in a new module. I renamed with a "UD" for updated.

    I added a few comment cards with asterick lines for some areas.

  • Re: Run-time Error Every Other Time The Macro Runs

    Thanks so much for taking a crack at it! I knew there had to be easier ways to do alot of what I'm trying to do, just didn't know what they were. I will pop this into another module and see what it does :)

    And as to your question about why I was pasting twice...Who knows! ;)

    I don't know if I'll be able to work on this today...I'm pretty busy, so it may be tomorrow or even Thursday before I get back to this, but I PROMISE I'll be back after I try this out!! (probably with more questions, but hey, why not...)

    To everyone who has helped me out so far: [COLOR="DarkRed"]THANK YOU FOR YOUR HELP!!![/COLOR] Everyone I have encountered on OzGrid has been really wonderful, and I and many other newbies totally appreciate your help!

  • Re: Run-time Error Every Other Time The Macro Runs

    JFrench, please don't ignore the rules you have agreed to and the text on the page you posted the new question from. Both clearly ask NOT to post any other questions in your Thread that do NOT match the Thread Title.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!