Select maximum date in month - Excel 2007 Pivot table drop down box macro

Experts,
I have a large workbook with several worksheets, each one contains a pivot table drop down box that has all dates in an SQL table that is brought into the spreadsheet via connection.  I need to have ALL dates in the drop down box available, BUT Only the last date of each month selected, AND the most recent date the data was processed.  I would like to do this for all worksheets in the workbook.

Example :  
The Pivot table drop down box Process_DT contains these dates :
But I only want  01/29/2010 and 02/26/2010 SELECTED and the other dates available.


Process_DT
01/04/2010
01/05/2010
01/06/2010
01/07/2010
01/08/2010
01/11/2010
01/12/2010
01/13/2010
01/14/2010
01/15/2010
01/18/2010
01/19/2010
01/20/2010
01/21/2010
01/22/2010
01/25/2010
01/26/2010
01/27/2010
01/28/2010
01/29/2010
02/01/2010
02/02/2010
02/03/2010
02/04/2010
02/05/2010
02/08/2010
02/09/2010
02/10/2010
02/11/2010
02/12/2010
02/15/2010
02/16/2010
02/17/2010
02/18/2010
02/19/2010
02/22/2010
02/23/2010
02/24/2010
02/25/2010
02/26/2010

Thank you for your help !
MITSWISWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nutschCommented:
Hi; try this code; making sure you have a reference to runtime in your vb editor.

Thomas

PS: from techbookreport: "The first thing to do is to create a reference to the Microsoft Scripting Runtime, which is the DLL that contains the Dictionary (as well as the FileSystemObject). To do that we open Excel, hit F11 to get to the Visual Basic environment and then select References from the Tools menu. Navigate down through the list of available references and select Microsoft Scripting Runtime (\Windows\system32\scrrun.dll). Once that's done the Dictionary is available for you to use in your code."

Sub Macro2()
Dim dictMonth As New Dictionary, i As Variant
Dim pt As PivotTable, pf As PivotField, sht As Worksheet, pi As PivotItem

Application.ScreenUpdating = False

For Each sht In ActiveWorkbook.Sheets
    For Each pt In sht.PivotTables
    
        Set pf = pt.PivotFields("Process_DT")
    
        pf.EnableMultiplePageItems = True
        
'        On Error Resume Next
        
        For Each pi In pf.PivotItems
            If dictMonth.Exists(Format(pi, "yyyymm")) Then
                If pi > CDate(dictMonth.Item(Format(pi, "yyyymm"))) Then
                    dictMonth.Item(Format(pi, "yyyymm")) = pi
                End If
            Else
                dictMonth.Add Format(pi, "yyyymm"), pi
            End If
        Next
        
        For Each pi In pf.PivotItems
            If pi = dictMonth.Item(Format(pi, "yyyymm")) Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        Next
        
    Next pt
Next sht

Application.ScreenUpdating = True
End Sub

Open in new window

0
MITSWISWAuthor Commented:
Hello Thomas,
Thank you so much for the code, it works until it encounters a runtime error '1004 Unable to set the visible property of the pivot item class' errors on the line

 Else
    pi.Visible = False

Is there a setting I need to change within the pivot field itself ?  Thank you for your help ! - Terry
   
-------------------------------------------------------------------------  
 For Each pi In pf.PivotItems
            If pi = dictMonth.Item(Format(pi, "yyyymm")) Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        Next
  -----------------------------------
0
nutschCommented:
Works on my side, can you load a sample workbook with that issue.

Thomas
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

MITSWISWAuthor Commented:
I am not able to do that as the spreadsheet is linked to a datasource with sensitive information.  The pivot column is displayed as a row, if that helps.
0
nutschCommented:
Is your date in a page field or not?
0
MITSWISWAuthor Commented:
I am attaching an excel spreadsheet with a screen shot of the Process_Dt field to show you how it is pivoted.... I hope this helps !
Sample2.xlsx
0
nutschCommented:
Can you run it on this sample file?

25841244.xls
0
MITSWISWAuthor Commented:
Yes that did work !  I am using Excel 2007, could that be any reason why this wouldn't work ?  Plus, there is another pivot field filtered in the same report, I am wondering if that has anything to do with it. Any ideas for Excel 2007 ?
0
nutschCommented:
Can you run this macro and see what the new sheets look like, they'll tell you what Excel is trying to hide or show.

I'm running XL07 too, so it's not the issue.

Thomas



Sub UpdatePageFieldsOnAllSheets()
'update all page fields all pivots on all sheets, using a dictionary to select the appropriate elements.

Dim dictMonth As New Dictionary, i As Variant
Dim pt As PivotTable, pf As PivotField, sht As Worksheet, pi As PivotItem

application.ScreenUpdating = False

For Each sht In ActiveWorkbook.Sheets
    For Each pt In sht.PivotTables
    
        Set pf = pt.PivotFields("Process_DT")
    
        pf.EnableMultiplePageItems = True
        
'        On Error Resume Next
        
        For Each pi In pf.PivotItems
            If dictMonth.Exists(Format(pi, "yyyymm")) Then
                If pi > CDate(dictMonth.item(Format(pi, "yyyymm"))) Then
                    dictMonth.item(Format(pi, "yyyymm")) = pi
                End If
            Else
                dictMonth.Add Format(pi, "yyyymm"), pi
            End If
        Next
        
        Dim shtResults As Worksheet
        
        Set shtResults = Sheets.Add
        shtResults.[a1] = "Date"
        shtResults.[B1] = "Hide?"
        
        For Each pi In pf.PivotItems
            shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = pi.Value
            If pi = dictMonth.item(Format(pi, "yyyymm")) Then
                shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "False"
                'pi.Visible = True
            Else
                shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "True"
                'pi.Visible = False
            End If
        Next
        
    Next pt
Next sht

application.ScreenUpdating = True
End Sub

Open in new window

0
MITSWISWAuthor Commented:
And, it successfully indicates the proper dates to hide as well as the proper dates to show  - How do I make this work with the Process_dt field of the pivot table ?
0
nutschCommented:
Ok, can you record a macro where you set the items visible or hidden, and then send me the code?

Also, I don't think it's a case issue, but can you validate that the following line has the right case for the Process_DT

        Set pf = pt.PivotFields("Process_DT")

Thomas
0
MITSWISWAuthor Commented:
The "Process_DT" field is correctly cased.

When I run the  code like this below the runtime error occurs when trying to set the visible property to False:
            If pi = dictMonth.Item(Format(pi, "yyyymm")) Then
                'shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "False"
                pi.Visible = True
               
            Else
                'shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "True"
    ---->            pi.Visible = False
            End If


When I run the code as below several other worksheets are added and the results are perfect with regard to the proper dates = TRUE or FALSE.

 If pi = dictMonth.Item(Format(pi, "yyyymm")) Then
            shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "False"
                      'pi.Visible = True
               
            Else
                shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "True"
                      ' pi.Visible = False
            End If


-----------------------------------------------------------------------------------------------------------------------
Full code :

Sub UpdatePageFieldsOnAllSheets()
'update all page fields all pivots on all sheets, using a dictionary to select the appropriate elements.
 
Dim dictMonth As New Dictionary, i As Variant
Dim pt As PivotTable, pf As PivotField, sht As Worksheet, pi As PivotItem
 
Application.ScreenUpdating = False
 
For Each sht In ActiveWorkbook.Sheets
    For Each pt In sht.PivotTables
     
        Set pf = pt.PivotFields("Process_DT")
     
        pf.EnableMultiplePageItems = True
         
'        On Error Resume Next
         
        For Each pi In pf.PivotItems
            If dictMonth.Exists(Format(pi, "yyyymm")) Then
                If pi > CDate(dictMonth.Item(Format(pi, "yyyymm"))) Then
                    dictMonth.Item(Format(pi, "yyyymm")) = pi
                End If
            Else
                dictMonth.Add Format(pi, "yyyymm"), pi
            End If
        Next
         
        Dim shtResults As Worksheet
         
        Set shtResults = Sheets.Add
        shtResults.[a1] = "Date"
        shtResults.[B1] = "Hide?"
         
        For Each pi In pf.PivotItems
            'shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = pi.Value
            If pi = dictMonth.Item(Format(pi, "yyyymm")) Then
                'shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "False"
                pi.Visible = True
               
            Else
                'shtResults.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = "True"
                pi.Visible = False
            End If
        Next
         
    Next pt
Next sht
 
Application.ScreenUpdating = True
End Sub


I am not sure what to do to avoid the runtime error.
Thank you for your patience !
0
nutschCommented:
Do you have the Process_DT field in the same spot on all your pivots or do you have pivots where it's not part of the structure (part of the data, but not visible)?

What happens if you add this line to the beginning of the macro.
On error resume next

Thomas
0
MITSWISWAuthor Commented:
Nothing happens when I add the line On error resume next.

I am wondering if the issue has to do with the fact that the Process_Dt field is formatted as a datetime field ?  Does the code below treat the value stored in variable pi as a string ? If so, would that affect the logic regarding the visible property ? When the line is highlighted at the break in code, "Type mismatch" appears ....

                            If pi = dictMonth.Item(Format(pi, "yyyymm"))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MITSWISWAuthor Commented:
I would like to try another approach to this problem.  I am attaching a zip file that contains an Access DB, an Excel spreadsheet of sample data that serves as the import tables for the Access DB, and a data connected resulting Excel spreadsheet that displays a filtered by specific dates pivot table, as well as a pivot table of one column containing the dates to filter the main pivot table by.  How is it possible to automatically filter a main pivot table that is connected to a data source, by another table in the datasource ?
FilterPivotTableDates.zip
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.