Specify pivot table to hide 'earlier than' grouped data start date

[KenY]

I have been making some good progress with creating charts summarising data
by use of pivot table and charts. These are generated from the raw data by a
macro that can be applied to many input data sets.

One problem I have not overcome relates to grouping of data so that I show
whole weeks, counting back from the date of lastest data. I can show a given
number of weeks by specifying the start date, but I cannot find the VBA code
that suppresses the data that is 'earlier than' the start date. If I was
driving the table manually, I would select the field drop down and deactivate
the '<01/01/2000' group. My code is shown below and the point of failure is
in the second last line where ("<" & Str(BVGrpStart)) was an attempt to
derive a text value based on the variable which contains the earliest date
that I want to show. When the brackets enclose a text string like
"<01/01/2000" it would work, but I need to drive the text from my date
variable.

I would be grateful for any help that can be offered - I have not been able
to find it in the usual VBA help files.

' group BV dates to start complete number of weeks prior to last BV end
date
Dim lastBVend, BVGrpStart As Date
lastBVend = Range("Customer_view!B1").Value
'show a maximum of 20 weeks data - 140 days
BVGrpStart = lastBVend - 139
Range("A6").Select
Selection.Group Start:=BVGrpStart, End:=True, By:=7,
Periods:=Array(False, False, False, True, False, False, False)

With ActiveSheet.PivotTables("PerfSummary").PivotFields("end date of BV
data")
.PivotItems("<" & Str(BVGrpStart)).Visible = False
End With

 

[Shailesh Shah]

Try this,

With ActiveSheet.PivotTables("PerfSummary").PivotFields("end date of BV
data")

'to hide first date starting with "<"
If Left(.PivotItems(1), 1) = "<" Then .PivotItems(1).Visible = False

'to hide last date starting with ">"
If Left(.PivotItems(.PivotItems.Count), 1) = ">" Then
..PivotItems(.PivotItems.Count).Visible = False

End With

or

use Dateserial function with your variable named BVGrpStart.


Regards,
Shah Shailesh
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)

*** Sent via Developersdex http://www.developersdex.com ***

 

[KenY]

Thanks - I used your first method and it works a treat