none
Microsoft Excel use related not developer related... My macro isn't working. Can someone help please? Thanks. RRS feed

  • Question

  • Trying to select a few numbers from a larger group of numbers in one cell, copy them, then paste them in another cell. Error comes up as "Runtime error '1004': Paste special Method of Range class fail". When I go to "debug", it shows this:

    Sub Macro1()
    '
    ' Macro1 Macro
    ' averages
    '
    ' Keyboard Shortcut: Ctrl+m
    '
        ActiveCell.Range("A1:A12").Select
        Selection.ClearContents
        ActiveWindow.ScrollColumn = 17
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        ActiveSheet.Range("$K$1:$O$279").AutoFilter Field:=1, Criteria1:=RGB(198, _
            239, 206), Operator:=xlFilterCellColor
        ActiveCell.Offset(0, -21).Range("A1:A208").Select
        Selection.Copy
        ActiveSheet.Range("$K$1:$O$279").AutoFilter Field:=1
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 17
        ActiveWindow.ScrollColumn = 18
        ActiveWindow.ScrollColumn = 19
        ActiveCell.Offset(0, 21).Range("A1:A12").Select
        ActiveCell.Range("A1:A12").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub

    How can I get my macro to cut and paste what I want without this error? The macro does EVERYTING I asked of it except paste. Thanks.



    • Edited by Daryl Zer0 Wednesday, October 29, 2014 7:09 PM
    Wednesday, October 29, 2014 7:07 PM

Answers

  • Hi Dary,

    Thanks for posting in MSDN forum.

    Whats's version of Excel are you using? As far as I know, this may be a know issue, plase try to defind a const variable for the xlPasteValues in the code below:

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

    You can get more detail from article below:
    XL2000: "PasteSpecial method of Range class failed" Error When Running a Macro

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 31, 2014 2:31 AM
    Moderator