VBA - 1004 Error #Ref

[F4TMAN]

Hello,

I'm back again, let me just say that I found this website to be very helpful.

I currently have a script that was created by an external source. I don't have access to that person anymore. When I try running the script its returning a 1004 error (see attachment). From what i can see certain data is suppose to be paste as values but for some reason its coming up as #ref error. Is there anyway I can go about this?


Thanks in advance

Sub Budget_Data()
'
' Budget_Data Macro
'
' Keyboard Shortcut: Ctrl+j
'
    Range("N5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("P&L Scrubbed").Select
    Range("A1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("A1").Select
    ActiveSheet.Range("$A$1:$I$3183").AutoFilter Field:=1, Criteria1:="="
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Rows("2:3183").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Selection.AutoFilter
End Sub

 

[Michael M]

If the range of cells in the copy area have formulas in them, when you paste to
Sheets("P&L Scrubbed").Range("A1")....the formulas are probably trying to refer to locations that don't exist...ie, anything pre N5 references.
Maybe this way

Range("N5").CurrentRegion.Copy
    Sheets("P&L Scrubbed").Range("A1").PasteSpecial Paste:=xlPasteValues
    Sheets("P&L Scrubbed").Select
    Range("A1").Select
    ActiveSheet.Range("$A$1:$I$3183").AutoFilter Field:=1, Criteria1:="="

 

[Michael M]

UNTESTED

Sub Budget_Data()
' Budget_Data Macro
' Keyboard Shortcut: Ctrl+j
Range("N5").CurrentRegion.Copy
Sheets("P&L Scrubbed").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("P&L Scrubbed").Select
Range("A1").Select
ActiveSheet.Range("$A$1:$I$3183").AutoFilter Field:=1, Criteria1:="="
Rows("2:3183").SpecialCells(xlCellTypeVisible).EntireRow.Delete
Selection.AutoFilter
End Sub