Solved

MS Excel 2007 Using a Combo Box or Index to populate Report Filters on multiple pivots on different workbooks

Posted on 2013-02-06
4
395 Views
Last Modified: 2013-02-08
Hello Experts,

I am trying to use a combo box to select the report filters on a couple of pivots.  I watched an old datapig video and have set up the combo box with a range and created an index on the ChooseName sheet in the attached workbook.  I then recorded a macro to change the report filters and am now stuck trying to edit the code.

I don't know how to make reference to the index (cell G1 on ChooseName sheet) or even whether I am using a sensible approach.

My aim is to select a name from the combo on ChooseName sheet and have wherever it says "Aldim Looret" in the code below changed to the Index name.

Can I ask for your help please.


Hello Experts,

I am trying to use a combo box to select the report filters on a couple of pivots.  I watched an old datapig video and have set up the combo box with a range and created an index on the ChooseName sheet in the attached workbook.  I then recorded a macro to change the report filters and am now stuck trying to edit the code.

I don't know how to make reference to the index (cell G1 on ChooseName sheet) or even whether I am using a sensible approach.

My aim is to select a name from the combo on ChooseName sheet and have wherever it says "Aldim Looret" in the code below changed to the Index name.

Can I ask for your help please.


Sub Macro5()
'
' Macro5 Macro
'

'
    Sheets("PivotOne").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").CurrentPage = _
        "Aldim Looret"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PursuitLead"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PursuitLead").CurrentPage _
        = "Aldim Looret"
    Sheets("PivotTwo").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Name").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Name").CurrentPage = _
        "Aldim Looret"
    Range("B16").Select
End Sub
0
Comment
Question by:jay0kay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 38863176
Hi jay can you attach your workbook and I'll have a look at this
...Terry
0
 

Author Comment

by:jay0kay
ID: 38863277
Oops,  yep,  forgot to attach workbook.  Here it is
EE-Query.xlsx
0
 
LVL 16

Accepted Solution

by:
terencino earned 250 total points
ID: 38863523
Right, this macro will run on the DropDown1_Change event. It just picks up the name from G1 in the ChooseName sheet you have already done, and applies that as a variable through the macro you recorded. You were only a step away from completing this yourself, well done! Also the file extension has to change to XLSM for it to contain macros.
...Terry
Sub DropDown1_Change()
ChooseName = Sheets("ChooseName").Range("G1").Value
With Worksheets("PivotOne").PivotTables("PivotTable1")
    .PivotFields("Name").CurrentPage = ChooseName
    .PivotFields("PursuitLead").CurrentPage = ChooseName
End With
With Worksheets("PivotTwo").PivotTables("PivotTable2")
    .PivotFields("Name").CurrentPage = ChooseName
    .PivotFields("PursuitLead").CurrentPage = ChooseName
End With
End Sub

Open in new window

EE-Query.xlsm
0
 

Author Closing Comment

by:jay0kay
ID: 38867589
Thank you Terry,  both for the rapidly returned worked example and encouragement!  All very much apreciated.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question