This is a fantastic little macro that I picked up from The Spreadsheet Guru

I seem to use the GETPIVOTDATA function on a near daily basis to generate fixed format reports, it is such a flexible tool for creating dynamic reports that are formatted just as you require - without the limitations of the original pivot table, but just occasionally I have a problem with it.

So for example I was trying to whizz through some planning templates for 5 CCG’s and I was copy and pasting GETPIVOTDATA formulas across each, only to find the dreaded #REF error appearing all over the place because I was referencing a field name that didn’t exist in one of the CCG’s source pivot tables.

It’s easy to fix by wrapping the GETPIVOTDATA formula in its own error detection wrapper - the IFERROR function, but I had a lot of formulas to wrap which made the prospect rather daunting.

This macro was a life saver.

Copy it to a module in your personal macro workbook and you can use it across any workbook you have open. To use it, just select the cell or range with the formulas you want to adjust and then run the macro.

Sub WrapIfError()

'PURPOSE: Add an IFERROR() Function around all the selected cells' formulas
'SOURCE: www.TheSpreadsheetGuru.com

Dim rng As Range
Dim cell As Range
Dim x As String

'Determine if a single cell or range is selected
  If Selection.Cells.Count = 1 Then
    Set rng = Selection
    If Not rng.HasFormula Then GoTo NoFormulas
  Else
    'Get Range of Cells that Only Contain Formulas
      On Error GoTo NoFormulas
        Set rng = Selection.SpecialCells(xlCellTypeFormulas)
      On Error GoTo 0
  End If

'Loop Through Each Cell in Range and add =IFERROR(\[formula\],"")
  For Each cell In rng.Cells
    x = cell.Formula
    cell = "=IFERROR(" & Right(x, Len(x) - 1) & "," & Chr(34) & Chr(34) & ")"
  Next cell

Exit Sub

'Error Handler
NoFormulas:
  MsgBox "There were no formulas found in your selection!"

End Sub