Overwrite filtered table with values

Question

I am trying to overwrite a filtered table with its own values. Using this code, i have got as far as filter and copy, but it errors on the paste with this message, "PasteSpecial method of range class failed".

ExpectedFeesTbl.Range.AutoFilter Field:=3, Criteria1:="F"
ExpectedFeesTbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
ExpectedFeesTbl.DataBodyRange.SpecialCells(xlCellTypeVisible).PasteSpecial xlPasteValues

What I would like it to do is then paste in the values.

Answer 1

This does not work because you cannot paste in non-continous ranges.

The solution is to loop through the areas of the visible cells and replace the formulas with values in every area.

Dim VisibleCells As Range
Set VisibleCells = ExpectedFeesTbl.DataBodyRange.SpecialCells(xlCellTypeVisible)

If Not VisibleCells Is Nothing Then
    Dim iArea As Range
    For Each iArea In VisibleCells.Areas  ' loop through all areas
        iArea.Cells.Value = iArea.Cells.Value  ' convert all formulas in each area into values
    Next iArea
End If