OleMiss2010
Member
I'm having some trouble with the macro below in the following line:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Alpha!R1C1:R22556C21", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Pivot Table", TableName:="PivotTableAlpha", DefaultVersion _
:=xlPivotTableVersion10
Can anyone point out what the issue might be with this line? This is a generic macro I am creating as sort of a test.
Second question is does the rest of the macro look like it will work?
Here's the entire macro.
Sub PivotTableCreator()
'
' PivotTableCreator Macro
'
'
Sheets.Add
Sheets(2).Select
Sheets(2).Move After:=Sheets(3)
Sheets(3).Select
Sheets(3).Name = "Pivot Table"
Sheets(2).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Alpha!R1C1:R22556C21", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Pivot Table", TableName:="PivotTableAlpha", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Pivot Table").Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Code")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Balance"), "Sum of Balance", _
xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Salesman")
.Orientation = xlRowField
.Position = 3
End With
ActiveWindow.SmallScroll Down:=3
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Account"), "Sum of Account", xlSum
With ActiveSheet.PivotTables("PivotTable3").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Issues"), "Issues", xlSum
ActiveWindow.SmallScroll Down:=-6
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Code")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Risk")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Salesman")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of Account")
.Caption = "Count of Account"
.Function = xlCount
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Alpha!R1C1:R22556C21", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Pivot Table", TableName:="PivotTableAlpha", DefaultVersion _
:=xlPivotTableVersion10
Can anyone point out what the issue might be with this line? This is a generic macro I am creating as sort of a test.
Second question is does the rest of the macro look like it will work?
Here's the entire macro.
Sub PivotTableCreator()
'
' PivotTableCreator Macro
'
'
Sheets.Add
Sheets(2).Select
Sheets(2).Move After:=Sheets(3)
Sheets(3).Select
Sheets(3).Name = "Pivot Table"
Sheets(2).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Alpha!R1C1:R22556C21", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Pivot Table", TableName:="PivotTableAlpha", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Pivot Table").Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Code")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Balance"), "Sum of Balance", _
xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Salesman")
.Orientation = xlRowField
.Position = 3
End With
ActiveWindow.SmallScroll Down:=3
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Account"), "Sum of Account", xlSum
With ActiveSheet.PivotTables("PivotTable3").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Issues"), "Issues", xlSum
ActiveWindow.SmallScroll Down:=-6
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Code")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Risk")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Salesman")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of Account")
.Caption = "Count of Account"
.Function = xlCount
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub