Pivot table filters trough vba from cell value

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
505
Office Version
  1. 2013
Platform
  1. Windows
Hi Board.

ok so i don't even know how to begin going about this or if even possible so i will try and explain as best as possible....

i have a massive "database" report where i filter all my data and compress what i need in to pivot tables on that same file...

i have a series of other reports that depend on the results of the pivot tables from the "database" report.

So the other reports we'll name report 1 report 2 report 3 etc...

on my day to day i go to the database report and export data from other applications and paste it in to the "database" report then i refresh the pivot tables and change the dates accordingly, later i'll open report 1 and go back to the database report and filter the pivots by "report1" then copy paste data on to report 1 then save and close the report 1

then open report 2 go back to the database and filter by "report2" copy paste data on to report 2 save and close report 2

Same for report 3....


ok now....

What i'm trying to do is:

in reports 1,2,3 they all have their respective names in a cell,

so i want to see if its possible to create a macro in the personal workbook that reads said cell then goes back to the "database" report and change the filters on the pivots so i can then just copy paste the data faster without having to change one by one pivots...

Each pivot table has their own name inb_campaign, out_campaign, total_in_count, total_out_count etc...

P.s. they are 2 filters total that will need to be modified, Queue, and Campaign.

Is this even possible?

Thanx in advance.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
ok so after searching here is what i have so far...

Rich (BB code):
Sub Pivit()


 
'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
 
'Here you amend to suit your data
Set pt = Worksheets("Inbound Detail").PivotTables("Inbound_daily_summary")
Set Field = pt.PivotFields("Campaña")
NewCat = Worksheets("Inbound detail").Range("Z4").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
 
 
'Here you amend to suit your data
Set pt = Worksheets("Agent Count Pivots").PivotTables("Inbound_hourly")
Set Field = pt.PivotFields("Campaña")
NewCat = Worksheets("Inbound detail").Range("Z4").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
 
  
End Sub

But after adding the lines in orange the code now fails....

Any suggestions?
 
Upvote 0
ok so turns out my pivots on the second sheet is not a regular pivot table apparently.....

when i created this pivot i selected the option to "add this data to the Data model"

and apparently seems to be different, and i confirmed this by recording 2 simple macros changing the same filters on both pivots and looking at the results


Here is the pivot table created with "add this data to the Data model"

Code:
Sub tested()
    ActiveSheet.PivotTables("Inbound_hourly").PivotFields("[Range1].[Queue].[Queue]").ClearAllFilters
    ActiveSheet.PivotTables("Inbound_hourly").PivotFields("[Range1].[Queue].[Queue]").CurrentPageName ="[Range1].[Queue].&[Canopco]"
End Sub


and the Regular pivot table

Code:
Sub test2()
    ActiveSheet.PivotTables("Inbound_daily_summary").PivotFields("Queue").ClearAllFilters
    ActiveSheet.PivotTables("Inbound_daily_summary").PivotFields("Queue").CurrentPage = "Canopco"
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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