Why won't my macro execute fully?

Question

Hi,

I have a macro to copy and paste data from one cell(s) to another. it is assigned to a button to run. when I hit the button most of the macro executes perfectly, but one command does not work. it will fully execute when I run it from the VBA module but not when using the button. I have highlighted the problem area below. any assistance would be greatly appreciated as it is really doing my head in. thanks in advance.

ActiveWindow.SmallScroll Down:=7

    Range("H84").Select

    Application.CutCopyMode = False

    Selection.Copy

    ActiveWindow.SmallScroll Down:=-57

    Range("E4").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    ActiveWindow.SmallScroll Down:=37

    Range("H85").Select

    Application.CutCopyMode = False

    Selection.Copy

    Range("E40").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    ActiveWindow.SmallScroll Down:=25

    Range("E87").Select

    Application.CutCopyMode = False

    Selection.Copy

    Range("E85").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    ActiveWindow.SmallScroll Down:=-33

    Range("H39").Select

    Application.CutCopyMode = False

    Selection.Copy

    ActiveWindow.SmallScroll Down:=-21

    Range("D16").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Answer 1

thanks Bernie,

I'm almost too embarrassed to say what the problem was but it was because I had 2 modules with the same code and I was changing the cell reference in the module that was not assigned to the execute button. your shortened code has helped me tidy things up a bit and has also broadened my understanding somewhat. thanks again

That code is the same as

 

    Range("H84").Copy

    Range("E4").PasteSpecial Paste:=xlPasteValues

    Range("H85").Copy

    Range("E40").PasteSpecial Paste:=xlPasteValues

    Range("E87").Copy

    Range("E85").PasteSpecial Paste:=xlPasteValues

    Range("H39").Copy

    Range("D16").PasteSpecial Paste:=xlPasteValues

and I don't see any issues with it.

Answer 2

hi Jeeped,

I found where the error was and as a new user your shortened code has helped my VBA education immensely. thanks for your help

You can pass the value of a cell to another without a .PasteSpecial Paste:=xlPasteValues operation by simple assignment. For example:

    Range("E4") = Range("H84").Value

Range("E40") = Range("H85").Value

Range("E85") = Range("E87").Value

Range("D16") = Range("H39").Value

Answer 3

You can pass the value of a cell to another without a .PasteSpecial Paste:=xlPasteValues operation by simple assignment. For example:

    Range("E4") = Range("H84").Value

Range("E40") = Range("H85").Value

Range("E85") = Range("E87").Value

Range("D16") = Range("H39").Value

Answer 4

That code is the same as

    Range("H84").Copy

    Range("E4").PasteSpecial Paste:=xlPasteValues

    Range("H85").Copy

    Range("E40").PasteSpecial Paste:=xlPasteValues

    Range("E87").Copy

    Range("E85").PasteSpecial Paste:=xlPasteValues

    Range("H39").Copy

    Range("D16").PasteSpecial Paste:=xlPasteValues

and I don't see any issues with it.