|
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 |
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 |
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 |
| All times are GMT +1. The time now is 10:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com