Filker Pivot table between 2 dates

[wayneryeven]

Hey all
trying to write a macro that will remove any date filters from a pivot table and then filter the Date field again between 2 variables- dStart and dEnd

Pivot table is called PivotTable1. Field is called Date.

Cell B2 contains the Date filter.

I am not sure how to do this- i tried recording a macro to see what it generated but it was to no avail. The dates will change daily so i need it to 1. remove any date filter and 2. Filter to only show dates between dStart and dEnd.

Any and all help greatly appreciated to point me in the right direction.

Thanks

 

[kjv1611]

I think you may need to look at this differently. You say the filter will change day by day, and will be between 2 dates? So, are you looking at say the previous 30 days or something? In other words, are your 2 dates always something like this:
Date1 = Today minus 30 days
Date2 = Today minus 1 day

That way you basically get the previous full 30 days, I suppose.

So, for that, your filter would need to use a variable representing today, and then subtracting the number of days for each portion. That should work better, I would think, than deleting and recreating the whole filter each time. Of course, I've also done very little with pivot tables so far, so take my suggestions with a grain of salt.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Loomah]

hi
you should probably post this in the vba forum (forum707).

just an initial thought though (without looking into how to do this with a pivot table alone) i'd look at creating a subset of your data using msquery and then pivoting that.

as i say, just a thought!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

Use the Group Feature in the PivotTable, assuming that ALL the values in your date column are populated with real dates and not strings.

Then turn on your macro recorder and record setting the limits for you date in the Group feature. Post the recorded code in forum707 for customization help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]