Two questions about this macro I created

[OleMiss2010]

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

 

[Luke M]

Two tweaks to problem line. First, need to perform an "add" method, not create. Second, table destination needs to be complete workbook,sheet,cell address. E.g.

[pre]

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Alpha!R1C1:R22556C21").CreatePivotTable TableDestination:="'[Book1]Pivot Table'!R1C1", TableName:="PivotTableAlpha", _
DefaultVersion:=xlPivotTableVersion10
As for the rest of it, looks like it should work. You can remove all of the scrollwindow lines, as they are purely visual. For speed, I'd also add

Application.ScreenUpdating = False

[/pre]
to the begining and then reset it to true at the end.

 

[OleMiss2010]

Thanks. I appreciate the help.