I have a workbook which is designed to refresh named ranges based on pivot table data. When the pivot table refreshes, the named range is reset based on the new values which appear in the pivot table.
I am getting the error "Object Variable or with Block variable not set" when it tries to set the range to a named variable, is there something i'm doing wrong here which I cannot see?
Sub RefreshPivots()
Dim PT1 As PivotTable
Dim NameFirst As Range
Dim Config As Worksheet
'Define Worksheet
Set Config = ThisWorkbook.Worksheets("Config")
'Define Pivot Table Ranges
Set PT1 = Config.PivotTables("Pivottable1")
'Clear Current Filters
PT1.PivotFields("Number").ClearAllFilters
'Refresh Pivots
ThisWorkbook.RefreshAll
'Set Pivot Table Filters
PT1.PivotFields("Number").PivotItems("1").Visible = True
PT1.PivotFields("Number").PivotItems("2").Visible = False
PT1.PivotFields("Number").PivotItems("3").Visible = False
PT1.PivotFields("Number").PivotItems("4").Visible = False
PT1.PivotFields("Number").PivotItems("5").Visible = False
PT1.PivotFields("Number").PivotItems("6").Visible = False
PT1.PivotFields("Number").PivotItems("(blank)").Visible = False
'Remove Old Range
ThisWorkbook.Names("First").Delete
'Define Range for new Name (**This is where the error occurs**)
NameFirst = Config.Range("G4:G" & Config.Range("G" & Rows.count).End(xlUp).Row) **<--Error**
'Apply New Named Range
Range(NameFirst).Name = "First"
End Sub