1

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

1 Answer 1

3

Try SETting the range object.

SET NameFirst = Config.Range("G4:G" & Config.Range("G" & Rows.count).End(xlUp).Row)
NameFirst.Name = "First"
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for that! I have to remember ranges need to be set, I always use integers to count the cells and then use them to refer to a range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.