Macro works, then fails on second run, but if i click End and then run it again, it works again. WHY

  • What the subject says. Just wondering if anyone has had this experience before?

    - Workbook has numerous macros and steps to automate a process.

    - When I run through all steps the first time, everything works perfectly.

    - I close the file and save.

    - When I open the file and re-run the process with new days' data, I get an error message on step 10

    - other steps had data i cleared and then pasted new day data into, and all other macros work fine.

    - step 10 error happens on PasteSpecial command (see below)

    - i'm just copying some column data from one filtered table into another table that i had cleared.

    - I can choose "End" or "Debug" on the error message. I choose "End"

    - Without changing anything, I simply kick of step 10 again and it works perfectly now.

    It's an easy thing to do, but I don't want my users to have to do this obviously. I recently upgraded to Win 365.

    Has anyone experienced something like this and know what causes it?

  • PasteSpecial should be qualified with what should be pasted.

    For example

    PasteSpecial xlPasteValues


    PasteSpecial xlPasteFormats


    PasteSpecial xlPasteAll


    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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