VBA code to change Pivot Table "Report Filter" from userform textbox/combobox

[manishc1989]

Hi All,

I hope someone can help me with this. I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. Is this possible and can anyone help me with this? I've attached an excel file as an example. I've all looked on internet but couldn't find any solution.

Quick reply will be highly appreciated.

Regards,
Manish Chopra

 

[Smitty]

Welcome to the Board!

If you're using Excel 2010+ you can use a Slicer.

Here's an example of doing it with a TextBox value:

<font face=Calibri>                <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pt <SPAN style="color:#00007F">In</SPAN> Sheets(1).PivotTables<br>                    <SPAN style="color:#00007F">With</SPAN> pt.PivotFields("Physician ID")<br>                        .ClearAllFilters<br>                        .CurrentPage = TextBox1.Value<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> pt</FONT>

HTH,

 

[manishc1989]

Smitty Thanks for your reply. Really appreciated. I'm working in Excel 2007. Actually I'm working on a macro which pops up the two comboboxes which have same list showing in the pivot table report filters. Please let me know if you need any more clarification.

Sample Data

ID	NAME, INIT	GENDER	DEPT	SALARY	DOH	LOCATION	RAISE
1	Smith, J.	F	Sales	$41,250.00	2/2/1982	Boston	$45,375.00
2	Gazit, I.	M	Sales	$77,948.68	7/5/2010	Marlboro	$85,743.55
3	Roth, T.	F	Planning	$37,400.00	6/2/2008	Boston	$41,140.00
4	Carey, C.	M	Planning	$33,674.30	7/4/2007	Cambridge	$37,041.73

<tbody>
</tbody>

Here is my code :


Sub CreatePivot()

Dim objTable As PivotTable, objField As PivotField


ActiveWorkbook.Sheets("Employees Data").Select
Range("A1").Select

Set objTable = Sheet1.PivotTableWizard

Set objField = objTable.PivotFields("DEPT")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("LOCATION")
objField.Orientation = xlColumnField

Set objField = objTable.PivotFields("SALARY")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"

'Values to be Changed by user in Combobox1
Set objField = objTable.PivotFields("DOH")
objField.Orientation = xlPageField

'Values to be Changed by user in Combobox2
Set objField = objTable.PivotFields("GENDER")
objField.Orientation = xlPageField

End Sub


Error :
Private Sub ComboBox1_Change()
ComboBox1.Value = objTable.PivotFields("DOH")
End Sub

 

[ezazmmr]

Dear All,

could you plz help me with this, i've tried but failed, i need the same thing, im unable to attached file, suggest me & help me plz
TIA

 

[erickamiller143]

I am in the process of doing the same thing. I have not done this before but I figured it out by recording a macro first (Developer -> Record Macro). Hit Record Macro, change the pivot table filter and then hit stop recording.
In the file I have I got this macro

ActiveSheet.PivotTables("PivotTable3").PivotFields("Location").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Location").CurrentPage = _
        "Whatever Name"
        
        'This gives me the name of the pivot table and the name of the pivot field'
        'Change the Whatever name to the value or the variable and copy and paste into your code'

Copy and paste the recorded macro into your code.