pivot tables

[adam curtis]

Hopefully some one can help me solve the problem I am having creating a pivot table for a previously created pivot table

The code for creating the first pivot table is:

************************************************
Sub CreateMonthPivot(

' CreateMonthPivot Macr
' Macro recorded 29/04/2004 by Adam Curti

Dim PTCache As PivotCach
Dim PT As PivotTabl

Application.ScreenUpdating = Fals

'Delete MonthPivotSheet if it exist
On Error Resume Nex
Application.DisplayAlerts = Fals
Sheets("MonthPivotSheet").Delet
On Error GoTo

'Create a Pivot Cach
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:="'2004Febcase'!R1C1:R694C11"


'add new workshee
Worksheets.Ad
ActiveSheet.Name = "MonthPivotSheet

'Create the pivot table from the cach
Set PT = PTCache.CreatePivotTable(
TableDestination:=Sheets("MonthPivotSheet").Range("A1"),
TableName:="FebResults"

ActiveWorkbook.Sheets("MonthPivotSheet").PivotTables("FebResults").NullString = "0
With P
'add field
.PivotFields("Case ID").Orientation = xlRowFiel
.PivotFields("Start Date Time").Orientation = xlRowFiel
.PivotFields("Start Date Time").Subtotals
= Array(False, False, False, False, False, False, False, False, False, False, False, False

.PivotFields("Start Date Time2").Orientation = xlRowFiel
.PivotFields("Time Type").Orientation = xlColumnFiel
.PivotFields("WorkLoad Hrs").Orientation = xlDataFiel

End Wit
Application.ScreenUpdating = Tru

ActiveSheet.Range("B3").Group Start:=True, End:=True, By:=1, Periods:=Array(False,
False, False, True, False, False, False

ThisWorkbook.Names.Add Name:="rngPivData2",
RefersTo:="=OFFSET(MonthPivotSheet!$A$2,0,0,COUNTA(MonthPivotSheet!$G:$G) - 1,COUNTA(MonthPivotSheet!$A2:$II2))", Visible:=Tru

Call feb01visabl
Call CreateFeb01Pivo

Call feb02visabl
Call CreateFeb02Pivo

Call feb03visabl
Call CreateFeb03Pivo

End Su
*************************************
The problem I am getting is on calling createFeb03Pivot. The code in createFeb03Pivot is the same for createFeb01Pivot and feb02. All the code is doing is pivoting the data in the first table on a date and creating a new pivot table using some of the results

The code for createFeb03Pivot is:

**************************************
Sub CreateFeb03Pivot(

Dim PTCache As PivotCach
Dim PT As PivotTabl
Dim startpoint As Strin

Application.ScreenUpdating = Fals

ThisWorkbook.Names.Add Name:="rngPivData2",
RefersTo:="=OFFSET(MonthPivotSheet!$A$2,0,0,COUNTA(MonthPivotSheet!$G:$G) - 1,COUNTA(MonthPivotSheet!$A2:$II2))", Visible:=Tru

'MsgBox (ActiveWorkbook.Names("rngPivData2").Value
'Create a Pivot Cach
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=ActiveWorkbook.Names("rngPivData2").Name

Dim Bcell As Rang
For Each Bcell In Range(lastPostion & ":A62220"
If IsEmpty(Bcell) Then Exit Fo
Next Bcel

startpoint = ActiveWorkbook.Sheets("DayPivotSheet").Range(Bcell.Address).Offset(2, 0).Addres
lastPostion = startpoin
'Create the pivot table from the cach
Set PT = PTCache.CreatePivotTable(
TableDestination:=Sheets("DayPivotSheet").Range(startpoint),
TableName:="Feb03Results2"


With P
'add field
.PivotFields("Case ID").Orientation = xlRowFiel
.PivotFields("Case ID").Subtotals
= Array(False, False, False, False, False, False, False, False, False, False, False, False

.PivotFields("Start Date Time2").Orientation = xlRowFiel
.PivotFields("Start Date Time2").Subtotals
= Array(False, False, False, False, False, False, False, False, False, False, False, False

.PivotFields("Grand Total").Orientation = xlDataFiel
.PivotFields("Case ID").PivotItems("(Blank)").Visible = Fals

End Wit
Application.ScreenUpdating = Tru


End Su
************************************

Now feb01 and feb02 work fine but feb03 doesn't work. The table is created but for some reason it does not pick up the grand total field. I have tested using the named range manually with the wizard and the pivot table for feb03 is created fine but the code I have written doesn't seem to want to pick up the grandtotal field??