Share via

macro to select next value in a pivot table filter

Anonymous
2022-07-07T17:30:45+00:00

I have a macro that will select the pivot table filter value. But I have to hard code the filter value in the macro. I want the macro to select the next value in the pivot table. I have 200 values in the pivot table and I don't want to have to hard code all those values. And it's possible those values might change month to month. I also want to have the pivot table data to be copy/paste into a new worksheet in separate columns by the filter value without hardcoding the column for each filter value.

    Sheets("pivot").Select

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Retailer Name"). _

        ClearAllFilters

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Retailer Name"). _

        CurrentPage = "Albertson's 155"

    Range("B2").Select

    Selection.Copy

    Sheets("fbp").Select

    Range("B1").Select

    ActiveSheet.Paste

    Sheets("pivot").Select

    Range("E5:E18").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("fbp").Select

    Range("B3").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("pivot").Select

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Retailer Name"). _

        ClearAllFilters

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Retailer Name"). _

        CurrentPage = "Albertson's 341"

    Range("B2").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("fbp").Select

    Range("C1").Select

    ActiveSheet.Paste

    Sheets("pivot").Select

    Range("E5:E18").Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("fbp").Select

    Range("C3").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-07-07T19:41:35+00:00

    Try it like this: Replace "Data Sheet" with the name of the tab with your data set, and update Table1 to the actual table name. If you are not using an Excel Table, convert the range to a table.

    Option Explicit

    Sub TestMacro() 

    Dim R As Range 

Dim C As Range 

Dim i As Long 

With Worksheets("Data Sheet").Range("Table1[Retailer Name]") 

    .Copy .Cells(.Cells.Count).Offset(3) 

    Set R = .Cells(.Cells.Count).Offset(3).CurrentRegion 

    R.RemoveDuplicates Columns:=1, Header:=xlNo 

    Set R = .Cells(.Cells.Count).Offset(3).CurrentRegion 

End With 

i = 2 

For Each C In R 

    With Sheets("pivot") 

        With .PivotTables("PivotTable1").PivotFields("Retailer Name") 

            .ClearAllFilters 

            .CurrentPage = C.Value 

        End With 

        Sheets("fbp").Cells(1, i).Value = .Range("B2").Value 

        .Range(.Range("E5"), .Cells(.Rows.Count, "E").End(xlUp)).Copy 

        Sheets("fbp").Cells(3, i).PasteSpecial xlPasteValues 

        i = i + 1 

    End With 

Next C 

R.Clear

    End Sub

    0 comments