Pivot Table Macro

[PE]

I have recorded a macro (using relative references) which creats a pivot
table but when I run it I get a runtime error 5 and on clicking debug the
second line/paragraph is highlighted:

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Unpaid Invoices (2)!R2C1:R164C5", Version:=xlPivotTableVersion12).
_
CreatePivotTable TableDestination:="Sheet2!R3C1",
TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion12

I don't know what is causing the macro to breakdown immediately after
creating a new sheet.

Would appreciate your help.

 

[MIKE215]

Hi PE

Assume the data starts in B3 on the UNPAIDINVOICE sheet and you would like
to put the Pivot Table in E12 on the new sheet. Leave the table destination
and table names arguement blank to start. That should avoid the error you
are getting now You can fill them in later in the code. Select range B3 and
set SRange equal to the current region. Use DRange to position the pivot
table on the new sheet that the pivot wizard method adds.

Sub AddPivot()
Dim SRange As Range
Dim DRange As Range

Sheets("UnpaidInvoices").Select
Range("B3").Select
Set SRange = ActiveCell.CurrentRegion

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
SRange).CreatePivotTable TableDestination:="", TableName:= _
"", DefaultVersion:=xlPivotTableVersion12

Set DRange = Range("e12")

With ActiveSheet
.PivotTableWizard TableDestination:=DRange
.PivotTables(1).Name = "Unpaid Invoices"
.PivotTables(1).AddFields RowFields:="X", ColumnFields:="Y"
.PivotTables(1).PivotFields("Z").Orientation = xlDataField
End With

ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False

Set SRange = Nothing
Set DRange = Nothing
End Sub

Regards,
Mike