Pivot Table Field via List

Alkemist

Board Regular
Joined
Nov 26, 2003
Messages
144
Hia Everyone,

I'm using a workbook that contains 3 pivot tables. One field in all pivot tables is the same. I would like to create something like a list box on the first worksheet that feeds to this field of the pivot tables.

The idea is that a user can select an item from a list box on the front sheet which will then change the field setting of all the pivot tables in the workbook and refresh them.

I figured I could set up a button on the first sheet that when a user clicks it, a macro will activate that takes the value selected from the list box, feeds it to the pivot tables and updates them all.

I recorded a macro that I thought would do the job but it doesn't!

ActiveSheet.PivotTables("PivotTable2").PivotFields("BB").CurrentPage = Range("I7").Select

I intended the pivot table to look in cell I7, change pivot filed BB to this value and then refresh.

Any ideas? Maybe a macro isn't needed for this and it can be done in regular pivot table settings?

Cheers...Alkemist
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
just answered my own question...

change VBA to:

ActiveSheet.PivotTables("PivotTable2").PivotFields("BB").CurrentPage = Range("I7").Value

That works fine...
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top