Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should this code work in Excel 2007 as it does in 2003?
This code below came from Debra Dalgleish:
http://www.contextures.com/excelfiles.html (Thank you Debra!). I'm not a coder, though excited about how easy this is to follow, so I want to get started on educating myself. However, applying it to my own Excel 2007 workbook is having inconsistent results. I saved my workbook as a Macro Enabled (.xlsm) file. I have 4 worksheets with a total of 6 Pivot Tables. They all share a field, located in the Report Filter section, called "Project_Status". There are two datasources feeding the workbook (only one of the pivot tables uses a different source), but all 6 of the "Project_Status" filters have exactly the same data choices. What I'm finding is that sometimes some of the fields are updated while others are not. Never a consistent pattern. I don't think I've once seen ALL 6 of the filters change. Sometimes they change, but not to what I asked for. They'll change to have multiple choices selected, not always including the one I picked in cell D2. I thought that maybe it didn't like any of these filters set to "Select Multiple Items". I removed that check from each of the six filter fields. I find that when I do, and then update cell D2 to test, some of the fields change back to "Select Multiple Items", but do not set the value correctly (I only want one value, not multiple). Strangely (or at least it seems so), one of my 4 worksheets does seem to update every single time. It's the one that has 3 pivot tables on it. I thought maybe each pivot table had to have a unique name (3 of the 6 were still called "PivotTable1"), but that didn't do it. Any thoughts or direction? Thanks! Tim Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim pt As PivotTable Dim pi As PivotItem Dim strField As String strField = "Region" On Error Resume Next Application.EnableEvents = False Application.ScreenUpdating = False If Target.Address = Range("D2").Address Then For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables With pt.PageFields(strField) For Each pi In .PivotItems If pi.Value = Target.Value Then .CurrentPage = Target.Value Exit For Else .CurrentPage = "(All)" End If Next pi End With Next pt Next ws End If Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Should this code work in Excel 2007 as it does in 2003?
A little more...
It appears that I had to manually choose each of the options for each of the pivot tables. In other words, this "Project_Status" field has 5 possible values. For the ones that weren't updating, I manually selected each of the five directly on the pivot table... and from that point on the update to cell D2 correctly updated that table every time. And then to another one I did the same thing, and had the same result. It "remembered" after a save, so now this works. Does that make sense?? I'm hoping still to understand. This is fine for this one with only 5 possible values, but I was hoping to do this on fields where the value could be any of thousands of numbers (order number, invoice number, etc)... so there would be no way to prep the file for this. "Tim Miller" wrote in message ... This code below came from Debra Dalgleish: http://www.contextures.com/excelfiles.html (Thank you Debra!). I'm not a coder, though excited about how easy this is to follow, so I want to get started on educating myself. However, applying it to my own Excel 2007 workbook is having inconsistent results. I saved my workbook as a Macro Enabled (.xlsm) file. I have 4 worksheets with a total of 6 Pivot Tables. They all share a field, located in the Report Filter section, called "Project_Status". There are two datasources feeding the workbook (only one of the pivot tables uses a different source), but all 6 of the "Project_Status" filters have exactly the same data choices. What I'm finding is that sometimes some of the fields are updated while others are not. Never a consistent pattern. I don't think I've once seen ALL 6 of the filters change. Sometimes they change, but not to what I asked for. They'll change to have multiple choices selected, not always including the one I picked in cell D2. I thought that maybe it didn't like any of these filters set to "Select Multiple Items". I removed that check from each of the six filter fields. I find that when I do, and then update cell D2 to test, some of the fields change back to "Select Multiple Items", but do not set the value correctly (I only want one value, not multiple). Strangely (or at least it seems so), one of my 4 worksheets does seem to update every single time. It's the one that has 3 pivot tables on it. I thought maybe each pivot table had to have a unique name (3 of the 6 were still called "PivotTable1"), but that didn't do it. Any thoughts or direction? Thanks! Tim Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim pt As PivotTable Dim pi As PivotItem Dim strField As String strField = "Region" On Error Resume Next Application.EnableEvents = False Application.ScreenUpdating = False If Target.Address = Range("D2").Address Then For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables With pt.PageFields(strField) For Each pi In .PivotItems If pi.Value = Target.Value Then .CurrentPage = Target.Value Exit For Else .CurrentPage = "(All)" End If Next pi End With Next pt Next ws End If Application.EnableEvents = True Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 help doesn't work. I got both Excel 2003 and 2007 insta | Excel Discussion (Misc queries) | |||
Can't get bar code font to work in EXCEL 2003 | Excel Discussion (Misc queries) | |||
Function dosn't work in Excel 2007 but did in 2003 | Excel Worksheet Functions | |||
Make Excel 2007 work like 2003 | New Users to Excel | |||
I was using Excel 2007, now 2003 and my Macros won't work | Excel Worksheet Functions |