none
Microsoft visual basic run-time error 1004 RRS feed

  • Question

  • Hi,

    I keep getting the following error message when I run a Macro and can't figure out what the problem is.

    Run-time error '1004':

    PasteSpecial method of Range class failed

    Part of the code is below, error in BOLD:

    Sub MARCH_TEST()
    '
    ' MARCH_TEST Macro
    ' MARCH TESTING OF FORMULAS
    '

    '
        Sheets("Control Sheet").Select
        Range("B120").Select
        Selection.Copy
        Sheets("Program Grids").Select
        ActiveSheet.Range("$B$2:$AJ$5897").AutoFilter Field:=7, Criteria1:="OH"
        Range("L15").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Sheets("Control Sheet").Select
        Range("B121").Select
        Selection.Copy
        Sheets("Program Grids").Select
        ActiveSheet.Range("$B$2:$AJ$5897").AutoFilter Field:=7, Criteria1:= _
            "Total Labor"
        Range("L16").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

    Any help would be great, thanks in advance!!

    Tuesday, April 18, 2017 2:06 PM

Answers

  • Try it like this:

    Sub MARCH_TEST()
        With Sheets("Program Grids")
            .Range("$B:$AJ").AutoFilter Field:=7, Criteria1:="OH"
            .Range(.Range("L2"), .Cells(.Rows.Count, "L").End(xlUp)) _
                .SpecialCells(xlCellTypeVisible).Value = Sheets("Control Sheet").Range("B120").Value
            .Range("$B:$AJ").AutoFilter
        End With
    End Sub

    • Marked as answer by J-Squared27 Wednesday, May 17, 2017 8:21 PM
    Tuesday, April 18, 2017 4:39 PM