How do I use a variable in Excel VBA pivot table when using value filtering?

Question

I have the following sub:

Sub test()

DeviceModel = "123 Model_name"

        ActiveSheet.PivotTables(DeviceModel).PivotFields("Baseline Release").AutoSort _
        xlDescending, "Count of Device ID"

        ActiveSheet.PivotTables(DeviceModel).PivotFields("Baseline Release"). _
        PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=ActiveSheet. _
        PivotTables(DeviceModel).PivotFields("Count of Device ID"), Value1:=10

End Sub

This part works:

        ActiveSheet.PivotTables(DeviceModel).PivotFields("Baseline Release").AutoSort _
        xlDescending, "Count of Device ID"

But the second part doesn't:

        ActiveSheet.PivotTables(DeviceModel).PivotFields("Baseline Release"). _
        PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=ActiveSheet. _
        PivotTables(DeviceModel).PivotFields("Count of Device ID"), Value1:=10

If I replace (DeviceModel) with ("123 Model_name") it works as expected:

        ActiveSheet.PivotTables("123 Model_name").PivotFields("Baseline Release"). _
        PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=ActiveSheet. _
        PivotTables("123 Model_name").PivotFields("Count of Device ID"), Value1:=10

It says: "application defined or object defined error"

I have done extensive googling and still cant figure out why it doesn't work with the variable instead of the actual table name.

Does a with ActiveSheet.PivotTables(DeviceModel).PivotFields("Baseline Release") have a similar result? — steegness
– steegness, Commented Apr 17, 2015 at 13:22
Perhaps excel is being a jerk about the variable not being declared first. Try adding Dim DeviceModel As String before you assign it a value. — JNevill
– JNevill, Commented Apr 17, 2015 at 13:38

Community · Accepted Answer · 2017-05-23 10:10:10Z

0

I found an answer in Excel VBA - Pivot Table with Multiple Value Filters

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables(DeviceModel)

With pvt.PivotFields("Baseline Release")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=pvt.PivotFields("Count of Device ID"), Value1:=10
End With

edited May 23, 2017 at 10:10

CommunityBot

11 silver badge

answered Apr 17, 2015 at 13:39

Jon Carlstedt

2,3335 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I use a variable in Excel VBA pivot table when using value filtering?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related