Automate Pivot Table with varing number of records

[Johnny]

I prepare reports using the Pivot Table function. My goal is to automate
preparation, but errors are encountered due, I think, to the fact that the
number of records (rows) varies with each report.

Accordingly, I have to format the Pivot Table manually each time a report is
prepared.

Can anyone suggest a way to automate the Pivot Table preparation or offer an
alternative suggestion?

Thanks for your responses.

Johnny

 

[RB Smissaert]

Just spent a bit of time automating a pivot and this is the relevant code
fragment:

'arr is a 2-D variant array
840 Range(Cells(2, 1), Cells(UBound(arr) + 2, 4)) = arr

860 lPivotCount = ActiveSheet.PivotTables.Count + 1
870 strPivotName = "PATIENT_PIVOT" & lPivotCount
880 strRangeAddress = Range(Cells(1), Cells(UBound(arr) + 2, 4)).Address

890 With Application
900 .DisplayAlerts = False
910 .ScreenUpdating = False
920 End With

'make the pivot table
'------------------------------
930 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=strRangeAddress).CreatePivotTable
_
TableDestination:=Cells(1), _
TableName:=strPivotName

940 With ActiveSheet.PivotTables(strPivotName)
950 With .PivotFields(strGPType)
960 .Orientation = xlRowField
970 .Position = 1
980 End With
990 With .PivotFields("SEX")
1000 .Orientation = xlRowField
1010 .Position = 1
1020 End With
1030 With .PivotFields("SURGERY")
1040 .Orientation = xlRowField
1050 .Position = 1
1060 End With
1070 With .PivotFields("AGE_GROUP")
1080 .Orientation = xlColumnField
1090 .Position = 1
1100 End With
1110 With .PivotFields("AGE_GROUP")
1120 .Orientation = xlDataField
1130 .Position = 1
1140 End With

1150 .RowGrand = True
1160 .GrandTotalName = "TOTAL"
1170 .Format xlTable1
1180 lLastPivotRow = .RowRange.Rows.Count + 1
1190 lLastPivotColumn = .ColumnRange.Columns.Count + 3
1200 End With

1210 Range(Cells(lLastPivotRow + 1, 1), _
Cells(lLastPivotRow + 1, 1).SpecialCells(xlLastCell)).Clear


This works well and you may need something similar.


RBS