Macro only works every other time..... Why?

[Mr_Musings]

Hi All

Im a fairly novice user of VBA. i have created a macro that is the culmination of a series of macros, as every time i did it in one go i managed to break it. however as smaller macro's i could manage them better. in the middle i have possible the simplest macro which is to copy a range in one worksheet and copy it to another worksheet. Code is as follows

Sub No7_MoveDataToAnalysis()
'
' No7_MoveDataToAnalysis Macro
'
    Sheets("Data").Select
    Range("B1:B3500").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
'
    End Sub

This works perfectly when i run this Macro on its own. However when i run it as part of the "Parent" macro, it only works every 2nd time.

why would this be and how do i correct this.

thanks
tom

 

[Andrew Poulsom]

What evidence do you have that it only works every second time?

 

[VoG]

Not sure but you don't need all that selecting

Sub No7_MoveDataToAnalysis()
'
' No7_MoveDataToAnalysis Macro
'
    Sheets("Data").Range("B1:B3500").Copy
    Sheets("Analysis").Range("A2").PasteSpecial Paste:=xlPasteValues
End Sub

 

[Mr_Musings]

thanks for the streamlining there VoG

Andrew, i have run it multiple times and every other time it works.

ironically before the streamlining it was working on the even attempts i.e. the second, fourth, sixth attempts, now ive added that coding in it works on odd attempts but not even

 

[Andrew Poulsom]

How are you calling that procedure? There is nothing in it that would cause it to run only every other time it is called.

 

[Mr_Musings]

im simply using

Application.Run("No7_MoveDataToAnalysis")

all the other macros in the sequence are called in the same way

 

[C.Andrews]

Last time I had this problem it was because my macro refreshed a data connection before processing the new information, and the macro would move on before the query finished refreshing. It would work the second time because the query had time to finish in-between attempts. I fixed it by disabling background refresh in the query.

 

[Mr_Musings]

Oh ok, thanks


Apologies for my ignorance, how do i do that please

 

[C.Andrews]

Oh ok, thanks


Apologies for my ignorance, how do i do that please

I'm using Excel 2010, so it might be different for you if you're using an earlier version; however what you need to do is open your spreadsheet and go to the Data tab. Somewhere on the left will be a button labelled "Existing Connections." Click that, and you should see your query(ies) listed under "Connections in this Workbook," right click which ever queries are used in the macro process and click "Edit Connection Properties."

The option to Enable/Disable background refresh is listed under the "Usage" tab.

Of course, if you're not refreshing any queries in your macro, this solution is worthless to you. Good luck!

 

[Mr_Musings]

I have no existing connections on the workbook.

are there any other possible solutions