Pivot Table Macro

  • Thread starter Thread starter PE
  • Start date Start date
P

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.
 
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
 
Hi Mike,

I am a new member to this site, and relatively new to excel macro scripts.

I have a similar issue with the pivot - getting error 5.

I tried using your recommendation and unfortunatel, could not run the script.

Can you pls advise, on what could be going wrong?


==Script that I configured for my use ===

Sub AddPivot()
Dim SRange As Range
Dim DRange As Range
Sheets("ClarityData").Select
Range("A1").Select
Set SRange = ActiveCell.CurrentRegion
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
SRange).CreatePivotTable TableDestination:="A3", TableName:= _
"MasterSchedule1", DefaultVersion:=xlPivotTableVersion12
Set DRange = Range("A3")
With ActiveSheet
.PivotTableWizard TableDestination:=DRange
.PivotTables(1).Name = "ClarityData"
.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
==========

Error is highlighted in Red Underline above.

Thank you in advance for your help!
 
Back
Top