| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Several Pivot Tables: Change one filter and adapt it for the other
Hey,
I have 6 Pivot Tables and don't want to change each of the filter seperately, to give me useful data. (e.g. change each one to the same date seperately). So I just want to change one filter and the other filters change to the same Value. So far I have this. I have 6 Pivot tables in one Sheet, just the Article numbers in another sheet, and all the data (inclusive article numbers again) in one more sheet. Can someone help me please? Dim x, n As Integer Dim r As Variant Dim art_no As Variant For x = 1 To 5 Select Case x Case 1 x = 11 Case 2 x = 12 Case 3 x = 13 Case 4 x = 14 Case 5 x = "ALL" End Select Set r = Worksheets("art").Range("A2:A1000") n = 1 For n = 1 To r.Rows.Count If r.Cells(n, 1) = "" Then Exit For End If art_no = r.Cells(n, 1) ActiveSheet.PivotTables("Sales,Receipt").PivotFiel ds("Store").CurrentPage = x ActiveSheet.PivotTables("Sales,Receipt").PivotFiel ds("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable3").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable3").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable1").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable1").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable4").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable4").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable2").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable2").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable7").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable7").PivotFields ("Art MGB").CurrentPage = art_no Next n Next x End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Several Pivot Tables: Change one filter and adapt it for the other
Hi
Take a look at the sample file http://www.contextures.com/PivotMult...CellChange.zip -- Regards Roger Govier "Aloha08" wrote in message ... Hey, I have 6 Pivot Tables and don't want to change each of the filter seperately, to give me useful data. (e.g. change each one to the same date seperately). So I just want to change one filter and the other filters change to the same Value. So far I have this. I have 6 Pivot tables in one Sheet, just the Article numbers in another sheet, and all the data (inclusive article numbers again) in one more sheet. Can someone help me please? Dim x, n As Integer Dim r As Variant Dim art_no As Variant For x = 1 To 5 Select Case x Case 1 x = 11 Case 2 x = 12 Case 3 x = 13 Case 4 x = 14 Case 5 x = "ALL" End Select Set r = Worksheets("art").Range("A2:A1000") n = 1 For n = 1 To r.Rows.Count If r.Cells(n, 1) = "" Then Exit For End If art_no = r.Cells(n, 1) ActiveSheet.PivotTables("Sales,Receipt").PivotFiel ds("Store").CurrentPage = x ActiveSheet.PivotTables("Sales,Receipt").PivotFiel ds("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable3").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable3").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable1").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable1").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable4").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable4").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable2").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable2").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable7").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable7").PivotFields ("Art MGB").CurrentPage = art_no Next n Next x End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Several Pivot Tables: Change one filter and adapt it for the o
You are great! THANKS!!!
"Roger Govier" wrote: Hi Take a look at the sample file http://www.contextures.com/PivotMult...CellChange.zip -- Regards Roger Govier "Aloha08" wrote in message ... Hey, I have 6 Pivot Tables and don't want to change each of the filter seperately, to give me useful data. (e.g. change each one to the same date seperately). So I just want to change one filter and the other filters change to the same Value. So far I have this. I have 6 Pivot tables in one Sheet, just the Article numbers in another sheet, and all the data (inclusive article numbers again) in one more sheet. Can someone help me please? Dim x, n As Integer Dim r As Variant Dim art_no As Variant For x = 1 To 5 Select Case x Case 1 x = 11 Case 2 x = 12 Case 3 x = 13 Case 4 x = 14 Case 5 x = "ALL" End Select Set r = Worksheets("art").Range("A2:A1000") n = 1 For n = 1 To r.Rows.Count If r.Cells(n, 1) = "" Then Exit For End If art_no = r.Cells(n, 1) ActiveSheet.PivotTables("Sales,Receipt").PivotFiel ds("Store").CurrentPage = x ActiveSheet.PivotTables("Sales,Receipt").PivotFiel ds("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable3").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable3").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable1").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable1").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable4").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable4").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable2").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable2").PivotFields ("Art MGB").CurrentPage = art_no ActiveSheet.PivotTables("PivotTable7").PivotFields ("Store").CurrentPage = x ActiveSheet.PivotTables("PivotTable7").PivotFields ("Art MGB").CurrentPage = art_no Next n Next x End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| pivot tables value filter | Excel Discussion (Misc queries) | |||
| How to filter field button items in pivot tables | Charts and Charting in Excel | |||
| Pivot Tables - filter out zero's? | Excel Discussion (Misc queries) | |||
| Customize Filter List in Pivot Tables | Excel Discussion (Misc queries) | |||
| How can I adapt the included filter example to search for more th. | Excel Discussion (Misc queries) | |||
Linear Mode
