If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Hello experts,
I've reviewed PT0021 Change All Page Fields sample Excel file (for 2007) on http://www.contextures.com/excelfiles.html#Pivot and am still having trouble with my complex workbook with several pivot tables/charts. I have found in this sample file that it works when you choose an explicit value in the filter, however when you re-set the filters back to 'all' or choose multiple values, this does not apply to subsequent pivot tables and worksheets. Is this only possible with explicit values in the main filter or can the code be modified to work when choosing 'all'? Also, my workbook contains several worksheets of data, and subsequent worksheets of various pivot tables for each set of data. The field names would be consistent across each worksheet of data, however in my ideal world, I'd like one main set of filters for a main pivot table to control ALL pivot tables on subsequent worksheets, which feed from a variety of data worksheets (all in the same workbook). Am I dreaming? So far, Excel pros that I've solicited help from using the sample file referenced above have not been able to make this work. Thank you. |
#2
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Hi Kathy
I think the problem is that (All) is not a pivot item when the code is cycling through the list of items belonging to a field. If you modify Debra's code to include the following 4 lines, then it will do what you want For Each pf In pt.PageFields If pf.Name = pfMain.Name Then For Each pi In pf.PivotItems ' inserted lines If pfMain.CurrentPage = "(All)" Then pf.CurrentPage = "(All)" Exit For End If ' end of inserted lines If pi.Name = pfMain.CurrentPage Then pf.CurrentPage = pi.Name Exit For End If Next pi End If I will drop Debra a note of this change, and if I am correct, then I am sure that she will modify her file on the website. -- Regards Roger Govier Kathy L. wrote: Hello experts, I've reviewed PT0021 Change All Page Fields sample Excel file (for 2007) on http://www.contextures.com/excelfiles.html#Pivot and am still having trouble with my complex workbook with several pivot tables/charts. I have found in this sample file that it works when you choose an explicit value in the filter, however when you re-set the filters back to 'all' or choose multiple values, this does not apply to subsequent pivot tables and worksheets. Is this only possible with explicit values in the main filter or can the code be modified to work when choosing 'all'? Also, my workbook contains several worksheets of data, and subsequent worksheets of various pivot tables for each set of data. The field names would be consistent across each worksheet of data, however in my ideal world, I'd like one main set of filters for a main pivot table to control ALL pivot tables on subsequent worksheets, which feed from a variety of data worksheets (all in the same workbook). Am I dreaming? So far, Excel pros that I've solicited help from using the sample file referenced above have not been able to make this work. Thank you. |
#3
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Excel 2007 PivotTable
Update Page Fields of multiple PTs. With "Select Multiple Items". With macro. http://c0718892.cdn.cloudfiles.racks.../04_10_10.xlsm Pdf preview: http://www.mediafire.com/file/znynzumj3jn/04_10_10.pdf For help on multiple sheets, upload your file. |
#4
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Hi Herbert
Regrettably, it fails each time for me with error 1004 on the line pvi.Visible = b(r, p, 2) -- Regards Roger Govier Herbert Seidenberg wrote: Excel 2007 PivotTable Update Page Fields of multiple PTs. With "Select Multiple Items". With macro. http://c0718892.cdn.cloudfiles.racks.../04_10_10.xlsm Pdf preview: http://www.mediafire.com/file/znynzumj3jn/04_10_10.pdf For help on multiple sheets, upload your file. |
#5
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Roger,
Could not duplicate error, but the index numbers of the PTs were scrambled. See Test(). Changed the line after For u=2 to t So now index numbers are ignored. Same cloud link. A collaboration on Kathy's #2 problem would be fun. Herb |
#6
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Hi Herbert
Still getting the problem. It errors 1004, Unable to set the visible property of the pivot item. It seems to me, that if the value of the item is already the same as what it is trying to be set, then it errors. Inserting 3 lines If pvi.Name = b(r, p, 1) Then If pvi.Visible b(r, p, 2) Then pvi.Visible = b(r, p, 2) End If End If enables it to run through for me. I still get problems though, as PT2 fails to get updated at all. I think we had best take this off line. You can email me at roger at technology4u dot co dot uk Change the at and dots to make valid email address -- Regards Roger Govier Herbert Seidenberg wrote: Roger, Could not duplicate error, but the index numbers of the PTs were scrambled. See Test(). Changed the line after For u=2 to t So now index numbers are ignored. Same cloud link. A collaboration on Kathy's #2 problem would be fun. Herb |
#7
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Roger,
My email attempts were blocked. The errors you correctly identified were dealt with in the current version (find PT6). Please download the file at the original cloud link. Herb |
#8
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Roger & Herbert - thank you both for your efforts. I apologize I didn't see
these replies sooner. If you're willing to collaborate and take a look at the 'challenge' what is the best way to get you a file? It's likely large to get you the full extent of what I'm trying to tackle. Thank you. "Herbert Seidenberg" wrote: Roger, My email attempts were blocked. The errors you correctly identified were dealt with in the current version (find PT6). Please download the file at the original cloud link. Herb . |
#9
|
|||
|
|||
Single Filter to change all Pivot Tables in a Workbook
Hi Kathy and Herbert
My attempts at emailing Herbert direct also failed. I know he had problems sending to me - but I don't understand why. Both of you can try again. roger at technology4u dot co dot uk Change the at to @ and the dot to . and eliminate all spaces. -- Regards Roger Govier Kathy L. wrote: Roger & Herbert - thank you both for your efforts. I apologize I didn't see these replies sooner. If you're willing to collaborate and take a look at the 'challenge' what is the best way to get you a file? It's likely large to get you the full extent of what I'm trying to tackle. Thank you. "Herbert Seidenberg" wrote: Roger, My email attempts were blocked. The errors you correctly identified were dealt with in the current version (find PT6). Please download the file at the original cloud link. Herb . |
Thread Tools | |
Display Modes | |
|
|