VBA help to update pivottable date filter with a single cell value date

Results 1 to 10 of 10

VBA help to update pivottable date filter with a single cell value date

This is a discussion on VBA help to update pivottable date filter with a single cell value date within the Excel Questions forums, part of the Question Forums category; My first post....
I have a single worksheet with multiple pivot tables. They all have a date range filter. I ...

VBA help to update pivottable date filter with a single cell value date

My first post....

I have a single worksheet with multiple pivot tables. They all have a date range filter. I want to insert vba code that will change the date filter for all the pivot tables on the sheet when I change a date in a single cell on the same sheet.

I am using Excel 2007 on Windows XP. I am pretty new to pivot tables, as well as VBA, but I'm learning quickly. The pivot tables I am working with have been inherited, so I didn't create them myself.

I've tried a few solutions I've found here and on the net, but they all vary greatly and didn't work. So I thought I'd start from scratch here.

Any suggestions? It seems this should be simple using pivotfilters.add type, but I'm stuck.

Re: VBA help to update pivottable date filter with a single cell value date

PivotFilters.Add works a little differently. Instead of making individual PivotItems Visible or Hidden, it uses a criteria string. That function would be a better fit if you wanted all dates after a specified date, or between a pair of dates.

Re: VBA help to update pivottable date filter with a single cell value date

Wow, that worked great! Thanks so much. I would never have been able to create that.

Question....

So I added to the vba code extra statements to have the same one cell date inserted as the filter to different date fields in four different pivot tables on the sheet. I need to go further with the same code to include around 50 different pivot tables in the sheet, all with different filter date fields, which all need to be changed to the date that's in cell B1.

The code is below. I changed it to include 4 pivot tables, each with a different date field name. Is there an easier way to do this, rather than adding dim statements for all 50 date fields, field name definitions, then call filter statements for each pivot table and field? Is there a way to combine these three tasks into one statement per pivot table?

As an alternative to coding the Variant Array values into the VBA procedure,
you might find it easier to maintain if you make a table in your worksheet that has all the pairs side-by-side.
This procedure could be modified to read in the values in that table range.

Re: VBA help to update pivottable date filter with a single cell value date

Originally Posted by Mary2012

I agree with you...a separate table on the sheet would be better, cleaner. So how would the code be modified to remove the array and have it point to the pivot table/date field table?

You could try this. I assumes you have a range named "FieldTable" on Sheet1 with that range having PivotTable Numbers in the first column, and
Field Names in the second. The range should not include any headers.

Re: VBA help to update pivottable date filter with a single cell value date

You are awesome! Thanks so much for your help! It took me a while to build the pivot table list - we have about 130 pivot tables on the sheet - but once I had everything in place it worked like a charm. I'm new on the job and scored a few points with my new co-workers, plus I learned a lot in the process. Thanks again!