Copying only values and not the formulas using copy destination code in VBA

Question

I would like to copy the cells that are left in the filtered table, but only copy their values not formulas. I used the cose below which works fine unless the filter only leaves one row in which I get the error "script out of range" for the [Sheets("Cost Accounting").Range(A2).PasteSpecial xlPasteValues] code line.

I attached an image of an example where the code will not paste (when there is only a single row left after the filter) Example Problem Scenario

Any ideas?

Sub DT_test()

Dim table As ListObject
Dim rngToCopy As Range

Sheets.Add.Name = ("Cost Accounting")
Sheets("Cost Accounting").Activate

TeamRole2 = "Cost Accounting"
MaxDate = Date

Set table = Worksheets("Overview").ListObjects("Table1")

table.Range.AutoFilter Field:=13, Criteria1:=TeamRole2
table.Range.AutoFilter Field:=8, Criteria1:="<" & MaxDate

On Error Resume Next
Set rngToCopy = table.DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rngToCopy Is Nothing Then
    rngToCopy.Copy
    Sheets("Cost Accounting").Range(A2).PasteSpecial xlPasteValues
End If

table.Range.AutoFilter

End Sub

Darrell H Darrell H · Accepted Answer · 2020-08-12T20:08:30

Try pasting the values...

If Not rngToCopy Is Nothing Then
    rngToCopy.Copy 
    Sheets("Cost Accounting").Range("A2").PasteSpecial xlPasteValues
End If

Copying only values and not the formulas using copy destination code in VBA

1 Answers