Improving PivotTable Efficiency

[Igwiz]

Hi again,

I am using a PivotTable to create some reports (using PivotSelect and Count etc). I actually want to look at the data month by month so I have written this

Sub RestrainPivotToMonths(WhichMonth As Integer)
On Error Resume Next
Windows("Transactions Master.xls&quot.Activate
With ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Tra-Transaction date&quot
For Each pit In .PivotItems
If Month(DateValue(pit)) = WhichMonth Then pit.Visible = True Else pit.Visible = False
Next
End With
End Sub

which will only include dates matching a desired month. ie RestrainPivotToMonths 6 will limit entries to those in June. However, this doesn't appear to be very efficient - I could add a column to the PivotData Source but the purpose is that I will eventually be able to Input a Start and To date to allow a query on any timeframe. Any suggestions on how best to improve this? Particularly, can you limit the PivotTable to only look at a subset of data in the first place? (I tried autofiltering the source data but with no luck!)

Thanks in advance and for all you helps so far.
Cheers,
Ig

 

[SkipVought]

Hi,

Depending upon which version of Excel you are running, you can use the built-in pivot table row or column filtering.

I just set up source data with 2 columns -- Date and Amount

I dragged the Date into the ROW area, Amount in DATA area

I then grouped Date by Month & Year.

I can then filter to show only the Year(s) and Month(s) I desire.

Am I missing something?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

I'm using Excel 2000 and can't seem to get Grouping to work. Says it cannot group this selection. Will give it another go.
Cheers,
Ig

 

[SkipVought]

Ig,

Are you sure that EACH AND EVERY VALUE UNDER DATE IN YOUR SOURCE DATA IS A DATE?

No empty cells
no other values

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com