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
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