VBA how to store a filter value of a pivot table into a variable

Matador_24

Board Regular
Joined
Dec 1, 2011
Messages
201
Hello,

I have a code extracting values from a pivot table.
My main filter is called INVESTMENT NUMBER in the pivot table.
The information in that field is a short code in the format: XXXX.XX
When I generate the pivot table, there can be dozens of numbers in that field, normally I choose the INVESTMENT NUMBER(S) I want to include. I have a couple of questions, once that I choose the investments I want:
1. What code can I use to store the INVESTMENT NUMBER I choose? For instance, if I choose the investment: 38, I want the variable InvNumber to store 38.
2. In case I choose more than one investment (the ones I choose will always start with the same number before the dot), I want to isolate the number before the dot (.) and store it in the variable. For instance, If I choose together: 1834.01, 1834.11, 1834.23, I need that the value stored in the variable to be 1834 only.

Thanks in advance for the help,

Luis
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Matador_24

Board Regular
Joined
Dec 1, 2011
Messages
201
Re: VBA how to store a filer value of a pivot table into a variable

Hello,

A little help over here will be appreciated,

Thanks!
 

Matador_24

Board Regular
Joined
Dec 1, 2011
Messages
201
Re: VBA how to store a filer value of a pivot table into a variable

Hello,

Literally I spent all day trying to figure this out searching online but I do not get to find something exact.
So far, I am able to read the items in the field I want (YEAR) and I list the items in a sheet, with a Loop I can go through each.
However, currently the code is listing ALL items even tough not all of them are selected.
How can I modify the code to read only the items that are selected in the filter?

Code:
Sub Test3()


Dim YearContent As Long


Worksheets("sheet4").Activate
r = 1
With Worksheets("sheet3").PivotTables(1)
        Cells(r, 1) = .PivotFields("Year").Name
        For x = 1 To .PivotFields("Year").PivotItems.Count
            r = r + 1
            Cells(r, 1) = .PivotFields(2).PivotItems(x).Name
            YearContent = .PivotFields(2).PivotItems(x).Name
            MsgBox YearContent
        Next
End With


End Sub

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top