• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Defaulting to Most Recent Entry in Pivot Filter

C

camrose

New Member
Hello,

This forum has been invaluable in assisting in me with a variety of Excel issues but I have a new situation for which I cannot find a solution.


I have a pivot table that pulls from a massive data source that contains financials based on a rolling 12 month average. I need to be able to filter one month at a time in the pivot to produce the report.

I am in search of a macro that will refresh the pivot and default to selecting the most recent month that appears in the pivot table filter list.


Currently, the pivot table contains a 'Date' in the filter field and because the data on each line for the date is a rolling 12 months of data, I need only select one month at a time to see the values for the past 12 months. What I need is to create a macro that will only place a checkmark in the most recent date that is returned when the pivot table is refreshed.

I have an example spreadsheet if anyone needs it (not sure how to attach it here).


Is this possible?


Any help is greatly appreciated!


Thanks,

Cameron


[Edited to add: I have tried creating a combo box referring to a dropdown list of months and pointing the output cell to a named range called selMonth and naming the cell in the pivot filter to fltMonth and applied the below macro


Sub updatePivot()

[fltMonth] = [selMonth]

End Sub


but it is not working...i'm not sure if it is having trouble because i am using a custom format for the date of YYYY-MM and not using the excel five digit date number or what]
 
Hi Cameron ,


It might help if you can upload your example spreadsheet ; unfortunately , this forum does not have any provision for attaching files to posts or topics , or uploading files.


What you can do is use any public file-sharing website such as RapidShare , SpeedyShare , ZippyShare , DropBox , GoogleDocs ,... to upload your file , give others permission to access and download your file , and then post the access link here , in this same topic.


Narayan
 
Great, thanks for the tip. I was wondering how sharing was possible.


Here is a link to the file

https://www.dropbox.com/s/25ba0vcf6je2037/chandoo%20example.xlsx


What I'm shooting for is a macro to make the YYYY-MM filter automatically default to the most recent (in the example file it would be 2013-06).


thanks!
 
Hi Cameron ,


Try this code in the ThisWorkbook section of the VBE.

[pre]
Code: 
Option Explicit
Dim mvPivotMonth As Variant
Dim mvPivotQtr As Variant

Private Sub Workbook_Open()
Dim pt As PivotTable
Dim pfQ As PivotField

'           On Error GoTo errHandler
Set pt = ThisWorkbook.Worksheets("Sheet4").PivotTables("PivotTable1")
Set pfQ = pt.PivotFields("YYYY-MM")

If LCase(pfQ.CurrentPage) <> LCase(mvPivotQtr) Then
Application.EnableEvents = False
pt.ManualUpdate = True
pfQ.ClearAllFilters
pfQ.CurrentPage = Format([Latest_YYMM], "yyyy-mm")
pt.ManualUpdate = False
mvPivotQtr = pfQ.CurrentPage
GoTo exitHandler
End If

exitHandler:
Set pt = Nothing
Set pfQ = Nothing
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Could not update fields"
Resume exitHandler
End Sub
[/pre]
Copied from here : http://www.contextures.com/excelpivottablereportfiltervba.html


Latest_YYMM is a named range which I have defined , referring to :


=Sheet1!$V$1


and Sheet1!$V$1 contains the formula : =MAX(Table1[YYYY-MM])


Narayan
 
Hi Narayan!

I appreciate your help, but I am getting an error:


"Run-Time Error '13':

Type Mismatch"


Does this mean anything to you?


Thanks again!
 
Hi ,


Check your file here :


https://www.dropbox.com/s/kdxx29d4bb6md04/chandoo%20example.xlsm


It means there is a mismatch between the formats of the two fields in the statement :


pfQ.CurrentPage = Format([Latest_YYMM], "yyyy-mm")


Can you see what is the difference between the various fields in the file you have and the file I have uploaded ?


Narayan
 
When I first opened yours, the format of the YYYY-MM filtered cell was set to (All) and was defaulting to General formatting.

Once I changed the selected one of the months in the filter, the format changed to Custom (yyyy-mm)


Though the same thing happens in mine, when I pasted the code, I believe there was a month already selected. When i change it back to (All), I no longer get the error, but I don't know how to make the macro run as there is nothing to select when I click on the Macros function in the ribbon.
 
Hi Cameron ,


I seem to have no problems at my end !


I select multiple items in the report filter , cell B2 changes to (Multiple Items) ; I save the file and exit ; when I reopen the file , I immediately get 2013-06 in B2 , and the pivot table amounts are correct.


Can you again upload your file , with your data and formats , and into which you have copied and pasted the code ?


Narayan
 
Hi Cameron ,


Check your file here :


https://www.dropbox.com/s/qxz9mw475wd0bwo/chandoo%20example%20mine.xlsm


The named range had not been defined ; that has been done now.


Narayan
 
Still being weird.

When I used yours it said Multiple Items, but it selected three different months (none of them were 2013-06)


So when I added a few more rows of data to the source data to see if it would change after i saved, closed and reopened, when i open the file, the filter for YYYY-MM has been removed. and in the field list it is now listed as 2012-04 not YYYY-MM?


I dont know what I'm doing to screw this up.
 
Hi Cameron ,


I am sorry but I really cannot help out !


The macro is a Workbook_Open macro , which will be executed only once , when you open the file.


Are you disabling macros when you run Excel ?


Narayan
 
Hi Cameron ,


I cannot say , since I am using 2007 ; however , I don't think so. Probably someone else who uses 2010 can confirm.


Narayan
 
You must log in or register to reply here.
Back
Top