Pivot Table VBA

[vmjan02]

I have the code below and its showing me the error as "Invalid Procedure call or argument"

Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, _
TableDestination:=Range("A1"), TableName:="PivotTable1") this code is the error

Sub Pivot()
Dim PCache As PivotCache, pt As PivotTable, rs As Worksheet

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Pivot").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set rs = Sheets("Sheet3")
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, _
SourceData:=rs.Range("A1").CurrentRegion.Address)


Worksheets.Add
ActiveSheet.Name = "Pivot"
ActiveWindow.DisplayGridlines = False
Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, _
TableDestination:=Range("A1"), TableName:="PivotTable1")

 

[RoryA]

There's nothing inherently wrong with the code syntax. Where is it located - i.e. which module?

 

[vmjan02]

no in the sheet.

 

[RoryA]

If it's in a worksheet code module, then change the destination argument to specify the sheet:

TableDestination:=Activesheet.Range("A1")

 

[vmjan02]

yes got it, I have put the code in module and it is all well, thanks. my miss.