Macro works stepping through, but not when run on its own

  • Hi there.


    I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.


    Here is the code:

    Code
    Sheets("Current Part Data").Select                                                                                              'Select the 'Current Part Data' worksheet
    
    
    'Copy the data to the 'Previous Part Data' worksheet then clear the data
     Range("A4:V" & CurrentPartDataFinalRow).Copy
     Sheets("Previous Part Data").(Range("A4").PasteSpecial Paste:=xlValues
     Range("A4:V" & CurrentPartDataFinalRow).ClearContents


    Again, run on its own, it does not paste the data into "Previous Part Data', but stepping through line by line works just fine. I need to only paste the values which is why I did not use 'copy destination: xxx'. I found that if I select the worksheet before pasting, then the code works fine as seen below:



    I have two questions:
    1. Why did this work in 2007 and not in 2013?
    2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?


    Cheers,
    JJ

  • Re: Macro works stepping through, but not when run on its own


    Try:


    Code
    With Sheets("Current Part Data").Range("A4:V" & CurrentPartDataFinalRow)
        .Copy
        Sheets("Previous Part Data").[a4].PasteSpecial xlValues
        .ClearContents
    End With

    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.

  • Re: Macro works stepping through, but not when run on its own


    Thank you. That seems to work, but why? Isn't my code doing the exact same thing as yours except you've used a 'With' statement? What happened in Excel 2013 to make this not work like it did in 2007? It's unfortunate that my three lines of code are now four. I realise it's only one line, but given how many times I do stuff like this, it adds up.

  • Re: Macro works stepping through, but not when run on its own


    Your code would or would not work depending upon which sheet was active when the code ran.


    You could change your code to:


    Code
    Sheets("Current Part Data").Range("A4:V" & CurrentPartDataFinalRow).Copy
    Sheets("Previous Part Data").[a4].PasteSpecial xlValues
    Sheets("Current Part Data").Range("A4:V" & CurrentPartDataFinalRow).ClearContents


    But using With.....End With makes the code much more readable


    If you have other code to do things with The Current Part Data sheet then you could use:


    Code
    With Sheets("Current Part Data")
        .Range("A4:V" & CurrentPartDataFinalRow).Copy
        Sheets("Previous Part Data").[a4].PasteSpecial xlValues
        .Range("A4:V" & CurrentPartDataFinalRow).ClearContents
        'other stuff for Current Part Sheet
    End With


    With......End With can be nested inside another With.........End With, so if there is code to do stuff to both sheets you could use:


    Code
    With Sheets("Current Part Data")
        .Range("A4:V" & CurrentPartDataFinalRow).Copy
        With Sheets("Previous Part Data")
            .[a4].PasteSpecial xlValues
            'other stuff for Previous Part Sheet
        End With
        .Range("A4:V" & CurrentPartDataFinalRow).ClearContents
        'other stuff for Current Part Sheet
    End With


    Both will work for whatever sheet is active at the time of code run.

    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.

  • I had a similar problem , and the solution I found was to use the line of conde :

    application.cutcopymode = false

    So you have to get rid of the "marching ants" , but it depends from code to code where this is proper to put it , you should experiment where putted it has the same result as the macro runned line by line.

  • royUK

    Closed the thread.

Participate now!

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