VBA efficiency question - restricting values in a pivot tabl

[wcassell]

I've got a VBA script that works, but I want to make it more efficient. Here's the setup:

Worksheet "Overall Performance" is a report that is linked to pivot table "Source Pivot" in the "Partner Pivot" worksheet. I also have 2 dropdown's in "Overall Performance" that prompt for a begining date and ending date that I'd like to see the report for. (These dropdowns populate cells "G3" and "G4") Once the user chooses the ending date from the dropdown I run the code below. As you can see there are 3 FOR/NEXT loops that turn on or off the date in the pivot table (therefore the report is automatically changed). This macro takes about 10-15 seconds to run, but I think it could probably be much faster. Any efficiency help would be appreciated.

Thanks, Will



Sub ddEnddate_click()

'The following variables were defined
'as date:
' min_date, max_date, begin_date, end_date, i,j,k

With ThisWorkbook.Worksheets("Overall Performance")
begin_date = Range("g3").Value
end_date = Range("g4").Value
End With

min_date = "01/01/2002"
max_date = "01/31/2002"

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = min_date To begin_date - 1
With ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").PivotFields("RECEIVED_DATE")
.PivotItems(i).Visible = False
End With
Next i


For j = begin_date To end_date
With ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").PivotFields("RECEIVED_DATE")
.PivotItems(j).Visible = True
End With
Next j


For k = end_date + 1 To max_date
With ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").PivotFields("RECEIVED_DATE")
.PivotItems(k).Visible = False
End With
Next k

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

End Sub

[dk]

Hi,

I don't think there's a huge amount you can do to your code to speed it up. As long as the variables are all defined as the correct type then I cannot think of a way to make any significant improvements using your current methods.

However, if you wanted to see some serious improvements in performance then here's an idea (but very involved)...

You can programatically set the PivotCache property of a pivot table. The PivotCache is where all of the data for the pivot table is stored. To set the pivot cache you need to create an ADO (ActiveX Data Objects) recordset and then set the PivotCache property to this. When you create the recordset you can use SQL to return only specific records e.g. records lying between begin_date and end_date. The creation of the recordset, assignment to the pivot cache and refresh of the pivot table would be rapid. I'm not sure if I've explained this very well but if you like I can email you a workbook which demonstrates these techniques.

Regards,
D

[Dave Hawley]

Hi

Use:

ThisWorkbook.Worksheets("Partner Pivot").PivotTables("Source Pivot").ManualUpdate = True

[Dave Hawley]

Oops, that may need to be False not True