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 !
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 !
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
-------------------------- ---------
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
Thomas
ASKER
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?
ASKER
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
Sample2.xlsx
ASKER
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
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
ASKER
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
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
ASKER
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.Cou nt, 1).End(xlUp).Offset(0, 1) = "False"
pi.Visible = True
Else
'shtResults.Cells(Rows.Cou nt, 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.Coun t, 1).End(xlUp).Offset(0, 1) = "False"
'pi.Visible = True
Else
shtResults.Cells(Rows.Coun t, 1).End(xlUp).Offset(0, 1) = "True"
' pi.Visible = False
End If
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
Full code :
Sub UpdatePageFieldsOnAllSheet s()
'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(Forma t(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.Cou nt, 1).End(xlUp).Offset(1, 0) = pi.Value
If pi = dictMonth.Item(Format(pi, "yyyymm")) Then
'shtResults.Cells(Rows.Cou nt, 1).End(xlUp).Offset(0, 1) = "False"
pi.Visible = True
Else
'shtResults.Cells(Rows.Cou nt, 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 !
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.Cou
pi.Visible = True
Else
'shtResults.Cells(Rows.Cou
----> 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.Coun
'pi.Visible = True
Else
shtResults.Cells(Rows.Coun
' pi.Visible = False
End If
--------------------------
Full code :
Sub UpdatePageFieldsOnAllSheet
'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
For Each sht In ActiveWorkbook.Sheets
For Each pt In sht.PivotTables
Set pf = pt.PivotFields("Process_DT
pf.EnableMultiplePageItems
' On Error Resume Next
For Each pi In pf.PivotItems
If dictMonth.Exists(Format(pi
If pi > CDate(dictMonth.Item(Forma
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.Cou
If pi = dictMonth.Item(Format(pi, "yyyymm")) Then
'shtResults.Cells(Rows.Cou
pi.Visible = True
Else
'shtResults.Cells(Rows.Cou
pi.Visible = False
End If
Next
Next pt
Next sht
Application.ScreenUpdating
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
What happens if you add this line to the beginning of the macro.
On error resume next
Thomas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
FilterPivotTableDates.zip
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.
Open in new window