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

Answer 1

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

Answer 2

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.

Answer 3

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