Invalid Procedure call or argument
 

Invalid Procedure call or argument when I run the following.
It seems to fail on the asterixed line.

Thks

Sub test()

x = Range("A50000").End(xlUp).Row
y = Range("IV1").End(xlToLeft).Column


Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
** "Original!" & Cells(1, 1).Address & ":" & Cells(x,
y).Address).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Cat4",
_
ColumnFields:="Account"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Amount").Orientation
= _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.Name = "Check"
End Sub


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=564608

Invalid Procedure call or argument
 

You may only need to change your constant on that line to
DefaultVersion:=xlPivotTableVersion10

instead of

DefaultVersion:=xlPivotTableVersion


but this is tested and worked for me:

Sub test()
With Worksheets("Original")
x = .Range("A50000").End(xlUp).Row
y = .Range("IV1").End(xlToLeft).Column

Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=.Range(.Cells(1, 1), _
.Cells(x, y))).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTableWizard TableDestination:= _
ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields _
RowFields:="Cat4", ColumnFields:="Account"

ActiveSheet.PivotTables("PivotTable1").PivotFields ( _
"Amount").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.Name = "Check"
End Sub

--
Regards,
Tom Ogilvy


"T De Villiers" wrote:


Invalid Procedure call or argument when I run the following.
It seems to fail on the asterixed line.

Thks

Sub test()

x = Range("A50000").End(xlUp).Row
y = Range("IV1").End(xlToLeft).Column


Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
** "Original!" & Cells(1, 1).Address & ":" & Cells(x,
y).Address).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Cat4",
_
ColumnFields:="Account"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Amount").Orientation
= _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.Name = "Check"
End Sub


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=564608