Hi All,
I had this Exact formula running well when I first entered it. But now that I have saved it and reopened it, it does not work. It errors out.
Everyday I download a lot of information out of AS400. Then I run create a pivot table. It seems simple but I can not get this to work now.
Can you help? Incidentally, the "PivotTable13" below will change to PivotTable14 next time I try to run. Then it will be PivotTable15 and so on. Is this part of problem? Thanks for your help.
'Select the range
Sheets("Sheet1").Select
maxrows = Sheets("Sheet1").UsedRange.Rows.Count
maxcolumns = Sheets("Sheet1").UsedRange.Columns.Count
Sheets("Sheet1").Range(Cells(1, 1), Cells(maxrows, maxcolumns)).Name = "Sheet1"
ActiveWindow.SmallScroll ToRight:=20
Range("Z1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1").CreatePivotTable TableDestination:= _
"[crap]Sheet1!R3C26", TableName:="PivotTable13", DefaultVersion:= _
xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable13").PivotFields("TRANS TYPE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable13").AddDataField ActiveSheet.PivotTables( _
"PivotTable13").PivotFields("tons"), "Sum of tons", xlSum
I had this Exact formula running well when I first entered it. But now that I have saved it and reopened it, it does not work. It errors out.
Everyday I download a lot of information out of AS400. Then I run create a pivot table. It seems simple but I can not get this to work now.
Can you help? Incidentally, the "PivotTable13" below will change to PivotTable14 next time I try to run. Then it will be PivotTable15 and so on. Is this part of problem? Thanks for your help.
'Select the range
Sheets("Sheet1").Select
maxrows = Sheets("Sheet1").UsedRange.Rows.Count
maxcolumns = Sheets("Sheet1").UsedRange.Columns.Count
Sheets("Sheet1").Range(Cells(1, 1), Cells(maxrows, maxcolumns)).Name = "Sheet1"
ActiveWindow.SmallScroll ToRight:=20
Range("Z1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1").CreatePivotTable TableDestination:= _
"[crap]Sheet1!R3C26", TableName:="PivotTable13", DefaultVersion:= _
xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable13").PivotFields("TRANS TYPE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable13").AddDataField ActiveSheet.PivotTables( _
"PivotTable13").PivotFields("tons"), "Sum of tons", xlSum