Trouble selecting date ranges with macro for pivot table from external source

VBA Rando

New Member
Joined
May 30, 2022
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
I want to start by saying I'm not very good with VBA and so I'm stuck on a small issue for a macro I'm trying to create which will select dates within a range based on inputting a Start Date and End Date in cells B1 & B2. So if I input Jan 1 and Jan 31, it should update the pivot table to select all dates within that range. The code currently looks as shown below. I tested code for a small simple pivot and it worked. The issue I'm having is that my actual pivot comes from an external source and gets to the point where it clears all date filters but it doesn't actually select all of the dates within the range I specify. I tried to record a macro to update to some dates to see how the code behaves and it gave me this:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]").VisibleItemsList = Array( _
"[XXX Show Dates].[Date].&[1950-01-04T00:00:00]", _

Any idea how I can get the below code to get past the clear filters and actually update the date ranges?

Sub PivotFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem
Dim StartDate As Date
Dim EndDate As Date

Sheets("VIP only").Activate
StartDate = Cells(1, 2)
EndDate = Cells(2, 2)

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]").ClearAllFilters
Set pvtF = Worksheets("VIP only").PivotTables("PivotTable1").PivotFields( _
"[XXX Show Dates].[Date].[Date]")

For Each pvtI In pvtF.PivotItems
If DateValue(pvtI.Name) >= StartDate And DateValue(pvtI.Name) <= EndDate Then
pvtI.Visible = True
Else
pvtI.Visible = False
End If
Next pvtI
End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,214,787
Messages
6,121,561
Members
449,038
Latest member
Guest1337

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top