Thank you very much in advance for your help.
I am having an excel VBA issue and it is driving me crazy. The code is below:
Sub ReceivingSummaryPivotTable()
Dim pt As PivotTable
Dim cacheOfPt As PivotCache 'this is the source data of the pt
Dim pf As PivotField
Dim pi As PivotItem
On Error Resume Next
Sheets("Receiving Summary").Select
ActiveSheet.pivottbales("ReceivingSummary").TableRange2.Clear 'deletes the PT
'set the cache of the PT
Sheets("Receiving").Select
Set cacheOfPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("ReceivingSourceData"))
'create the PT
Sheets("Receiving Summary").Select
Set pt = ActiveSheet.PivotTables.Add(cacheOfPt, Range("a1"), "ReceivingSummary")
'put the fields in
With pt
'add the fields
.PivotFields("Receive Site No").Orientation = xlRowField
.PivotFields("Receive Units SUM").Orientation = xlDataField
.PivotFields("Receive Amt SUM1").Orientation = xlDataField
'set the number format
Columns("B:B").NumberFormat = "#,##0"
Columns("C:C").NumberFormat = "#,##0.00"
'go to classic view
.RowAxisLayout xlTabularRow
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End With
End Sub
I know the code works because I have used it dozens of times. I know the range works because I have used that dozens of times. The only variable is that instead of fifty to sixty thousand lines of data I am trying to run it on 170,000.
Any thoughts? Thank you so very much.
Eddie
I am having an excel VBA issue and it is driving me crazy. The code is below:
Sub ReceivingSummaryPivotTable()
Dim pt As PivotTable
Dim cacheOfPt As PivotCache 'this is the source data of the pt
Dim pf As PivotField
Dim pi As PivotItem
On Error Resume Next
Sheets("Receiving Summary").Select
ActiveSheet.pivottbales("ReceivingSummary").TableRange2.Clear 'deletes the PT
'set the cache of the PT
Sheets("Receiving").Select
Set cacheOfPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("ReceivingSourceData"))
'create the PT
Sheets("Receiving Summary").Select
Set pt = ActiveSheet.PivotTables.Add(cacheOfPt, Range("a1"), "ReceivingSummary")
'put the fields in
With pt
'add the fields
.PivotFields("Receive Site No").Orientation = xlRowField
.PivotFields("Receive Units SUM").Orientation = xlDataField
.PivotFields("Receive Amt SUM1").Orientation = xlDataField
'set the number format
Columns("B:B").NumberFormat = "#,##0"
Columns("C:C").NumberFormat = "#,##0.00"
'go to classic view
.RowAxisLayout xlTabularRow
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End With
End Sub
I know the code works because I have used it dozens of times. I know the range works because I have used that dozens of times. The only variable is that instead of fifty to sixty thousand lines of data I am trying to run it on 170,000.
Any thoughts? Thank you so very much.
Eddie