VBA- Filter Pivot table based on latest and 2nd latest date in column

Hi All,

I'm trying to figure out how to write a macro for changing date in a pivot table.

I have a pivot table that I update every.

The pivot table data contains accumulated data.

This is the macro for refreshing the pivot table and changing the date to only show latest and 2nd latest dates' data

How do I make it dynamic so the macro will change the date to the next day

Thanks

I did a bit of modifying. I converted the data into excel tables simply because they are easier to work with. I created two named cells: LD_0 and LD_1 which are the latest date and next to latest date.

These dates need to be converted into strings that look like the format used in the pivot tables. Don't ask me why, but that's the way it is.

Setting the Filter for a report filter on a pivot table is easy. Just copy the value into the cell.

Setting filters for multi-selects are a bit more complicated. First you have to tell Excel what pivot table to update, then you have to tell it what field to filter on.

The code blows up if you try to turn off all the values for that filter. So if The first item is on and the second (and the rest of the items) are off and you turn the first item off, you will get an error. So this means, you need to turn everything on first, then you can turn off the selected items.

Please Login or Register  to view this content.

ClearPivot is a function that cleans and refreshes all the pivot tables in a workbook.

Please Login or Register  to view this content.

Hi,

thank you for your help, but when i try running both macro- i run clear filter first then I click set filter but i get error 1004, application defined or object defined error.

I may have missed something

On which line of the code did the error occur? Also did you change the workbook in any way?

Hi oh sorry it works very well now, no more errors, I will try to use this macro on my other works

May I know what is LD_0 and LD_1 (they don't seems to be a cell name) because I would like understand code so I can change the required to suit other workbooks

LD_O and LD_1 are named ranges. What I did was click in the cell with the latest date, Cell H2 on the data sheet and went to the name box (upper left where it tells you what cell you are in) and typed in LD_0 and pressed ENTER. That's the easiest way to define a static range. Highlight what you want, and type in its name. The only caveats in naming are the name must start with a letter, not have any spaces, not have any punctuation (other than underscores) and must not resolve to a cell reference such as END1.

There are a couple of reasons for giving a cell a name. The main one is that the name often makes more sense in a formula than a cell reference.

Another one is that the named range can be static so when you drag a formula down, you don't have to worry about the cell reference changing.

The reason I used it here is because VB code doesn't know when you add or remove columns or rows. So if the code was originally looking at Cell B2 and you added a column between A and B, then the code is looking in the wrong place. However. Excel does keep track where named ranges are, and you can reference them from the VB code as I did.

Finally, you can also give a name to a dynamic range: a range that changes size based on the data in it. This article explains how to do that: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

However, that's not as necessary as it used to be. Since you have Excel 2010, you also have Excel tables. They are their own dynamic ranges. This article explains tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Hi, I have tried to incorporate what I learnt into more complex worksheet (more pivots etc)) but I failed Can please help me take a look? Thank u so muchCTMS Compliance Report_test1.xlsm

I just took a quick look. I notice you are using slicers instead of pivot filters. Is this what you want set? Slicers work very similar to pivot filters. I will take a more detailed look when I get a chance.

We had an issue. When I first brought up the report, the date formats in the dropdown were all over the map. However, after refreshing the pivot table, it settled down to m/d/yyyy. I had to revise my code a bit since I'm now working with pivot charts instead of pivot tables.

What I do is loop through each worksheet and each chart. First I check to see if Run Date is used in the chart. If it is, then I process it. I use the same logic as with the pivot tables. Make each item visible, then turn of unwanted items. You might want to check to see if you want to include blank. You can see which line of code you have to change if you don't want to see blanks.

BTW: I played with your data for testing purposes.

I don't know what you are doing with the BI Report page, but you may consider converting the range into an Excel table. Excel tables know exactly how big they are, so you do not have to add extra rows that you may not need, and they "copy down" formulas from row to row.

Here is some information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Thanks for all the hardwork. I should have indicated which Pivot table should show 2 dates (latest and 2nd latest) and which pivot table only require to show latest date. For the charts, all will need to show 2 latest dates. I have reattached a document with yellow highlighted Field and written in more details

I tried running the SetFilter Macro- but it has a pop-up Debug- error on pi.Visible = True of this section. how can I resolve it?
' Turn on all items in filter
For Each pi In pf.PivotItems
pi.Visible = True

I assume that this is with the spreadsheet I sent you with no modifications. What error are you getting? I cannot get it to fail there.

Yup no changes, the clear filter works fine, but the set filter, it shows= compile error: Variable not defined , highlighting-
Next sh
Application.StatusBar = fales

so i change it to
Next sh
Application.StatusBar = false

Then i run set filter again- it shows Cannot execute code in break mode

I renamed the pivot tables PT_1 through PT_5 and made a table on sheet Tables to tell which get one date and which get two dates. Also, on one of the pivot tables, you changed the name of Run Date to "Total # of Update from" so I added an alias field to this table.

You now have two subroutines: one for the charts and another for these specific pivot tables.

Hi, just wondering if you encounter problem (errors) running the macros? because I click: 1st: Clear filter, 2nd: Set filter, 3rd: Set Chart filter, 4th: Set Pivotfilter. Upon clicking 1st Clear filter- There is error highlight- cannot execute in break mode pt.PivotCache.Refresh, click set chart filter- the pivottable field name is not valid, to create pivottable report, you must use data that is organised as a list with labelled column. If you are changing the name of a Pivot Tablefield, you must type a new name for the field. I

I did not do any modification to the table/macro/data

The only two macros that are "current" are SetChartFilter and SetPivotFilter. You can make another macro to call both of these and link it to a button.

Hi, I am really sorry but I still have problem running the macros without any modification: SetPivotFilter- Run time error '1004' Unable to set visible property of the PivotItem class,Highlight: pi.Visible = True
Then when i click SetChartfilter- the pivottable field name is not valid, to create pivottable report, you must use data that is organised as a list with labelled column. If you are changing the name of a Pivot Tablefield, you must type a new name for the field.

It worries me

Yes, if you change the name of the pivot table filter, you will have to change the code. However I think I have a table that tells you what the name of the pivot field is based on what the name pivot table is. The code reads the chart for these values so you don't have to change the code.

Hi, I did not make any modification to the worksheet you have created and attached, I download and try to run the macros but they didnt work out

Post the sheet here again with the current data. I'll see if I can replicate the problem.

Nothing has been modified in this excel, macros all the same from your last attached.