Link to home
Start Free TrialLog in
Avatar of MITSWISW
MITSWISW

asked on

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 !
Avatar of nutsch
nutsch
Flag of United States of America image

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

Avatar of MITSWISW
MITSWISW

ASKER

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
  -----------------------------------
Works on my side, can you load a sample workbook with that issue.

Thomas
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.
Is your date in a page field or not?
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
Can you run it on this sample file?

25841244.xls
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 ?
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

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 ?
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
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 !
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
ASKER CERTIFIED SOLUTION
Avatar of MITSWISW
MITSWISW

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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