Pivot table filters trough vba from cell value

[deletedalien]

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.

 

[deletedalien]

ok so after searching here is what i have so far...

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?

 

[deletedalien]

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"

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

Sub test2()
    ActiveSheet.PivotTables("Inbound_daily_summary").PivotFields("Queue").ClearAllFilters
    ActiveSheet.PivotTables("Inbound_daily_summary").PivotFields("Queue").CurrentPage = "Canopco"
End Sub

 

[deletedalien]

Bump