vba macro - sometimes it works, sometimes it doesn't

Question

This is my first macro, and I need some help. I keep changing my variables in sheet 1, and run another macro in sheet 2 to get my results. So this is a sensitivity test and I'm writing the following macro to run an already existing marco. Some of the rows it generates seem to be correct, but some of them are not. I can't figure out what went wrong. Any tips are appreciated.

Sub SensitivityTest()

For i = 8 To 11

    Range("G" & i + 1).Select
    Selection.Copy
    Range("D10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select

    Call AnotherMacro

    Range("Q76").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("H" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AD76").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("I" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("Q20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("J" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AD20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("K" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("Q27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("L" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AD27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("M" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("Q28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("N" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AD28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("O" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("V76").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("Q" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AI76").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("R" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("V20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("S" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AI20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("T" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("V27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("U" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AI27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("V" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("V28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("W" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("AI28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("X" & i + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Next i
End Sub

Woahhh buddy! I ***highly*** suggest you read through, and apply, [How to avoid using `.Select`/`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), as that can cause unexpected results. Long story short, to remove `.Select`, you "nudge" the two lines together that end in, and start with, `.Select/.Selection`. Ie: `Cells(1,1).Select // Selection.Value = "hello"` would just be `Cells(1,1).Value = "hello"`. Select is annoying because, as you may have noticed, if the wrong thing gets selected, **all** following lines will be incorrect. — BruceWayne, Oct 27 '16 at 22:15
Not sure why you got downvoted JBB, but I've made it a 0 for you again. Please read the link that BruceWayne has provided for you, and also read through YowE3K and Tim Williams' answers and see if the difference makes sense to you. I'm assuming that you've found the Macro Recorder! It's an amazing tool to help you find how Excel runs through things that you know how to do manually but not code. Keep in mind that the recorder will _always_ use `.Select`. It's best used as a starting place, and then refine your code properly like how YowE3K did. Hope you keep at it, good job so far :) — Tyeler, Oct 28 '16 at 01:32

score 2 · Answer 1 · answered Oct 27 '16 at 22:25

To follow up on @bruceWayne's comment:

Current copy/paste operation:

Sheets("Sheet2").Select
Range("AD76").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("I" & i + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Without selecting/activating:

Sheets("Sheet2").Range("AD76").Copy
Sheets("Sheet1").Range("I" & i + 1).PasteSpecial Paste:=xlPasteValues

YowE3K · Answer 2 · 2016-10-28T01:38:09.607

Because I was bored while eating my lunch, I decided to rewrite the code to see how much it would reduce to after getting rid of all the .Select, Selection. bits (plus a few other bits of tidying). I came up with this:

Sub SensitivityTest()

    With Sheets("Sheet1")

        For i = 8 To 11

            .Range("D10").Value = .Range("G" & i + 1).Value
            .Range("D15").Value = .Range("G" & i + 1).Value

            'This next line shouldn't be required if "AnotherMacro" was suitably changed
            'to fully qualify all ranges, etc, being referred to
            Sheets("Sheet2").Select

            Call AnotherMacro

            'Because the original code was pasting values, I have changed the
            'code to just set the destination cell's Value equal to the 
            'source cell's Value.  This avoids using the clipboard, which 
            'often leads to problems if the user is doing something else
            'while a macro is running.

            .Range("H" & i + 1).Value = Sheets("Sheet2").Range("Q76").Value
            .Range("I" & i + 1).Value = Sheets("Sheet2").Range("AD76").Value
            .Range("J" & i + 1).Value = Sheets("Sheet2").Range("Q20").Value
            .Range("K" & i + 1).Value = Sheets("Sheet2").Range("AD20").Value
            .Range("L" & i + 1).Value = Sheets("Sheet2").Range("Q27").Value
            .Range("M" & i + 1).Value = Sheets("Sheet2").Range("AD27").Value
            .Range("N" & i + 1).Value = Sheets("Sheet2").Range("Q28").Value
            .Range("O" & i + 1).Value = Sheets("Sheet2").Range("AD28").Value
            .Range("Q" & i + 1).Value = Sheets("Sheet2").Range("V76").Value
            .Range("R" & i + 1).Value = Sheets("Sheet2").Range("AI76").Value
            .Range("S" & i + 1).Value = Sheets("Sheet2").Range("V20").Value
            .Range("T" & i + 1).Value = Sheets("Sheet2").Range("AI20").Value
            .Range("U" & i + 1).Value = Sheets("Sheet2").Range("V27").Value
            .Range("V" & i + 1).Value = Sheets("Sheet2").Range("AI27").Value
            .Range("W" & i + 1).Value = Sheets("Sheet2").Range("V28").Value
            .Range("X" & i + 1).Value = Sheets("Sheet2").Range("AI28").Value

        Next i

        'Include a final select of Sheet1, just to get around the effect of
        'doing the Select of Sheet2 during the macro.  This wouldn't be 
        'needed if AnotherMacro was similarly tidied up to not require 
        'Sheet2 to be Selected before running.

        .Select

    End With

End Sub

I find this much easier to read, and therefore it would be a lot easier to maintain and debug when necessary.

P.S. All the i + 1 statements could be changed to just i if the loop was changed from For i = 8 To 11 to be For i = 9 To 12.

P.P.S. My guess as to why your code sometimes worked and sometimes didn't is that your code was dependent on Sheet1 being the active sheet when you invoked the macro. If Sheet2 was active, it would almost certainly not do what you wanted it to do.

Thank you for your help! It seems like it's still not calling the other macro correctly. The macro is on sheet 2, what does it mean to make sheet 1 active? — JBB, Oct 28 '16 at 18:37
oh it seems to be working now after I moved Sheets("Sheet2").Select to after calling another macro. Maybe that's what you meant? Thanks a lot : ) — JBB, Oct 28 '16 at 18:55
@JBB - (a) The "Active" sheet is the one that you are currently looking at. (b) What do you mean "the macro is on sheet 2"? Do you mean the code for `Sub AnotherMacro` is in the Worksheet code for Sheet2? If so, you will need to specify that and say `Call Worksheets("Sheet2").AnotherMacro`. (Unless code is working only on the object, e.g. the worksheet, the code should not be placed within the object's code - it would be better placed within a Module of the project. Then you don't have to worry about including the object name when calling the code.) — YowE3K, Oct 28 '16 at 19:03
@JBB - The only reason I left the `Sheets("Sheet2").Select` statement within the answer was because your original code was calling `AnotherMacro` while Sheet2 was selected and I didn't know whether `AnotherMacro` was doing anything on the "active" sheet (and thus would only work if that was Sheet2). If `AnotherMacro` works after you moved the Select statement, then the Select statement can be removed entirely. (And the one I put at the bottom of the code to ensure that Sheet1 was selected at the end can also be removed.) — YowE3K, Oct 28 '16 at 19:07
sorry, I think I spoke too soon. I tried to run more rows this time, and it got stuck towards the end, and the two values keep repeating themselves. — JBB, Oct 28 '16 at 20:50
hmm it wouldn't run after i added Call Worksheets("Sheet2").AnotherMacro — JBB, Oct 28 '16 at 21:04
@JBB - Where is `AnotherMacro` located? In Worksheets("Sheet1")'s code? In Worksheets("Sheet2")'s code? Or in a module? — YowE3K, Oct 28 '16 at 21:34
AnotherMacro has a button on sheet 2 : ) (That means it's on sheet 2, right?) — JBB, Oct 31 '16 at 14:09
@JBB - I assume you mean that a click of the button on sheet 2 invokes AnotherMacro. That doesn't mean that AnotherMacro's code is in the Sheet 2 worksheet - it could be in a module. Look through your code and see which module or worksheet it is in. If it is in a module (which would be the best place for it to be), just use `Call AnotherMacro`. — YowE3K, Oct 31 '16 at 14:57
I see ! It looks like it's one of the many Sub statements within a module. Thank you : D — JBB, Nov 01 '16 at 16:05

score 0 · Answer 3 · answered Oct 31 '16 at 14:19

Thank you all for the help! When I ran the following codes on Friday, it got stuck at the last few rows, and the same results kept repeating itself. But when I let it ran after work and not doing other things on the computer, it worked !

Sub SensitivityTest()

With Sheets("Sheet1")

    For i = 9 To 40

        .Range("D10").value = .Range("G" & i).value
        .Range("D15").value = .Range("G" & i).value

        Call AnotherMacro       

        .Range("H" & i).value = Sheets("Sheet2").Range("Q76").value
        .Range("I" & i).value = Sheets("Sheet2").Range("AD76").value
        .Range("J" & i).value = Sheets("Sheet2").Range("Q20").value
        .Range("K" & i).value = Sheets("Sheet2").Range("AD20").value
        .Range("L" & i).value = Sheets("Sheet2").Range("Q23").value
        .Range("M" & i).value = Sheets("Sheet2").Range("AD23").value
        .Range("N" & i).value = Sheets("Sheet2").Range("Q28").value
        .Range("O" & i).value = Sheets("Sheet2").Range("AD28").value
        .Range("Q" & i).value = Sheets("Sheet2").Range("V76").value
        .Range("R" & i).value = Sheets("Sheet2").Range("AI76").value
        .Range("S" & i).value = Sheets("Sheet2").Range("V20").value
        .Range("T" & i).value = Sheets("Sheet2").Range("AI20").value
        .Range("U" & i).value = Sheets("Sheet2").Range("V23").value
        .Range("V" & i).value = Sheets("Sheet2").Range("AI23").value
        .Range("W" & i).value = Sheets("Sheet2").Range("V28").value
        .Range("X" & i).value = Sheets("Sheet2").Range("AI28").value

    Next i      

End With

End Sub

Your comment `But when I let it ran after work and not doing other things on the computer, it worked !` makes me think that `AnotherMacro` is using `Selection` statements and therefore causing the issue. If the user manually Selects something between when the macro does a `Select` and then uses `Selection`, then the `Selection` will be using what the user selected rather than what the macro selected. — YowE3K, Oct 31 '16 at 15:01
Could you explain a little more? I looked at the codes in AnotherMacro and didn't see any Selection statements. Thanks! — JBB, Nov 01 '16 at 16:10
One of the common reasons for things working overnight, but not when someone is using the computer during the day, is errors caused by whatever the user is doing. So if your macro selects a cell (say Sheet1!B1), and then the user selects another cell (say Sheet2!D14), then the macro copies the selected cell to cell D2, what will happen is that Sheet2!D14 will get copied to Sheet2!D2, but the macro happily continues thinking it has copied Sheet1!B1 to Sheet1!D2. Copy/paste operations (inside or outside of Excel) also cause issues. — YowE3K, Nov 01 '16 at 19:06

vba macro - sometimes it works, sometimes it doesn't

3 Answers3