Pivot table filter change using VBA

[Danny]

Hello everyone

I am currently creating a large macro to run a report automatically, if you wouldn't mind sharing your wisdom.

I have a pivot table which takes data from a different worksheet and displays it. The data source is updated weekly. A range of dates are displayed, every week this range should shift forward by one week. This is normally done by me clicking on the filters and making sure the date range is correct.

It would be really helpful to have a script that would set the date filter each week, it would need to automatically update and shift forward by one week each week. The date range should be the the coming Saturday (eg 21/12/2013) till 8 weeks in advance (eg 15-Feb-2014).

Sometimes a wider data range is available in the in the filters than required.

If you need me to explain it more clearly please say

Thank you in advance!!

 

[BBD]

Hi Danny,

Below is the code you can use for it.

Sub Pivot_Filter()
'
Dim dt(1 To 8) As Date, i As Long
Dim pvt As PivotItem
dt(1) = Application.WorksheetFunction.Text(Now() + 7 - Weekday(Now), "mm/dd/yyyy")
For i = 2 To 8
dt(i) = dt(i - 1) + 7
Debug.Print dt(i)
Next i
'Change the sheet name, pivot table name and pivot field name as appropriate below.
For Each pvt In Sheets("sheet1").PivotTables("PivotTable2").PivotFields("FYDate").PivotItems
  For i = 1 To 8
  If pvt = dt(i) Then
  pvt.Visible = True
  Exit For
  Else: End If
  pvt.Visible = False
  Next i
Next pvt
End Sub

@ Experts : The code has loops which can be relatively slow. Please provide ur suggestions to make it efficient.

Cheers,
BD

 

[Danny]

Thank you very much for you help, I really appreciate it.

In using the code you have provided, it removes weeks before the 22nd which is correct (thank you) however it only then shows the week after, not 7 weeks ahead.

Is there something im doing wrong?

Thanks
Danny

 

[BBD]

Hi Danny,

If you can upload the file, it will be helpful for me to look at it.

 

[Danny]

I would do, unfortunately is work material and confidential, you code does work.

It sets the date correctly in terms of this Saturday. As the code runs it reduces the weeks one by one. It looks like it takes the first two weeks and then removes the third week then the forth, then the 5 etc.

Does it work correctly for you then?

 

[SirJB7]

Hi, Danny!
Any chance of uploading a sample file following the anonymizing instructions given here?
http://chandoo.org/forum/threads/new-users-please-read.294/
Regards!

 

[BBD]

Hi Danny,

It does work fine for me. As SirJB7 suggested, try to upload a sample with dummy data. It will let us understand about the issues you are facing.

 

[Danny]

Sorry for the late reply, I have been making the most of my time off, away from code and Excel.

Back in the office now, here is what im dealing with (dummy data). I have a range of dates, more than I actually need. I only want to have 8 weeks, starting from the approaching Saturday.

Hope this helps.

Thank you very much for all your help so far.