Macro to change pivot tables using combo box

[scju72]

Good morning,

I have a combo box built to control pivot table filters. It's working fine unless one of my combo box selections doesn't have data in the pivot being controlled. I'll either get an error message requesting END or Debug or it keeps the same data from the previous selection. I've added the code below. Do i need to include some type of clear statement if there is no match between the combo box selection and pivots?

The combo box works perfect for me unless there isn't a match with the pivot filters, etc. A copy of my code is below

Any assistance will be greatly appreciated. I'm still somewhat new with this type of function.

Thanks in advance.

Scott

Sub Sponsor_Name()
'
' SponsorNAME
'


'
Application.ScreenUpdating = False
Sheets("Data Sources").Visible = True
Application.ScreenUpdating = False
Sheets("Data Sources").Select
ActiveSheet.PivotTables("PVT2").PivotFields("SponsorName").CurrentPage = _
Range("A2").Text
ActiveSheet.PivotTables("PVT5").PivotFields("SponsorName").CurrentPage = _
Range("A2").Text
ActiveSheet.PivotTables("PVT7").PivotFields("Client Name").CurrentPage = _
Range("A2").Text
Sheets("Data Sources").Visible = False
Application.ScreenUpdating = False
Sheets("Dashboard Summary").Select

Mod Edit: code tags added

 

[Nebu]

Hi:

Why you want a macro to control multiple pivot tables? Can't you make use of slicers to do this.

Thanks

 

[scju72]

I hadn't used the slicers for this because i'm trying to combine all of the data in to a dashboard style document without having the ability to drill down to detail level data. Plus the data i'm presenting/combining isn't house in all of the same pivot tables.

 

[Nebu]

Hi:

Can you upload a sample file? However, if you are using Excel 2010 and above you can explore the possibility of using power pivot as well for combining multiple data sets into one and summarizing the data into a pivot.

Thanks

 

[scju72]

I will try to upload a sample file. Is power pivot an add on?? Would end users need to have Power pivot in order to use the file?

I have multiple data sources and pivots created. They're not all from the same data source and that's why i was doing it dashboard style where it's using formulas to populate the data in a summary file.

 

[Nebu]

Hi:

Power pivot is built-in from excel 2013 , for excel 2010 you will have to add it as an add-in which is available for free.

Thanks