Pivot Table Macro

[Kurt Lyons]

I am trying to write a program in excel that will take data from one sheet and create a pivot table on another worksheet within the same workbook. When I get to creating the pivot table portion of the prgram I get a run-time error '5' invalid procedure call or argument error

Please Login or Register  to view this content.

I have done pivot table macros before but never had this problem till now. What am I doing wrong?

[dilipandey]

Hi Kurt Lyons,

As an option, check if you have headings in all of the columns (including hidden one, if any)..

Post the sample file if you still have issues. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kurt Lyons]

Thank you for the response dilipandey. Yes I do have headers on all the columns. I am recording the macro as I do the pivot table function. But when I go to play what I recorded I get an error when it comes to making the pivot table.

I have done pivot tables in macros before under the last version of excel. Since our company upgraded to 2007 version I have not been able to record a pivot table macro that worked.

Here is a sample of the data I am drawing from to make the pivot table. I start my pivot table under the column heading "PF Item" and go all the way to "Shift".

ID Date Time PF Item Planning Family Description Unit Description SubUnit Description Reason Description Weight Cost TicketCreator Shift
45856 1/3/2012 6:12:00 PM CS24 12 OZ CARAMEL DIPPED SHORTBREAD Packaging Traypacker Traypacker take away 508 436.88 MURILLO, JUANA 2
45842 1/3/2012 6:40:00 AM CS24 12 OZ CARAMEL DIPPED SHORTBREAD Enrober/Depositor Enrober/Depositor Water Meter 592 509.12 CARNES, ROBERT 3
45843 1/3/2012 6:41:00 AM CS24 12 OZ CARAMEL DIPPED SHORTBREAD Enrober/Depositor Enrober/Depositor Cleanup 422 362.92 CARNES, ROBERT 3
45842 1/3/2012 6:40:00 AM CS24 12 OZ CARAMEL DIPPED SHORTBREAD Enrober/Depositor Enrober/Depositor Ovens Scrap 592 509.12 CARNES, ROBERT 3
45879 1/4/2012 8:57:00 AM SS32 13 OZ FUDGE STRIPED SHORTBREAD Packaging Traypacker Shutdown\Changeover 435 374.1 STARK, DALE L. 1
45906 1/4/2012 8:04:00 PM SF28 SF FDGE STRPED SHORTBREAD Enrober/Depositor Enrober/Depositor Leaking Pumps 1056 908.16 ESPINOZA, CHERI L. 2
45872 1/4/2012 5:52:00 AM SS32 13 OZ FUDGE STRIPED SHORTBREAD Packaging Traypacker Infeed Jam 501 430.86 PUFFER, LELAND 3
45867 1/4/2012 3:53:00 AM SSBE 13 OZ FUDGE STRPD SHORTBREAD Packaging Traypacker Infeed Jam 564 485.04 PUFFER, LELAND 3
45868 1/4/2012 3:54:00 AM SSBE 13 OZ FUDGE STRPD SHORTBREAD Packaging Traypacker Infeed Jam 561 482.46 PUFFER, LELAND 3
45953 1/5/2012 7:33:00 PM SS24 10.5 OZ Striped Shortbread Packaging Traypacker Traypacker take away 439 377.54 TRAN, KIMTHO T. 2
45959 1/5/2012 9:01:00 PM SS24 10.5 OZ Striped Shortbread Packaging Traypacker Traypacker take away 458 393.88 TRAN, KIMTHO T. 2
45960 1/5/2012 9:01:00 PM SS24 10.5 OZ Striped Shortbread Packaging Traypacker Traypacker take away 444 381.84 TRAN, KIMTHO T. 2
45922 1/5/2012 1:32:00 AM SS32 13 OZ FUDGE STRIPED SHORTBREAD Packaging Traypacker Infeed Jam 373 320.78 PUFFER, LELAND 2
45997 1/6/2012 9:50:00 PM SS24 10.5 OZ Striped Shortbread Packaging Traypacker Traypacker take away 250 215 TRAN, KIMTHO T. 2
46004 1/6/2012 11:47:00 PM SSBE 13 OZ FUDGE STRPD SHORTBREAD Packaging Traypacker Infeed Jam 393 337.98 STARK, DALE L. 2
46014 1/7/2012 6:59:00 AM SS32 13 OZ FUDGE STRIPED SHORTBREAD Enrober/Depositor Enrober/Depositor Leaking Pumps 753 647.58 MCCLURE, DOROTHY A. 3
46051 1/8/2012 10:26:00 AM SS24 10.5 OZ Striped Shortbread Machining Machining NA Catch pans 725 623.5 BENSON, OWEN F. 2
46039 1/8/2012 4:16:00 AM SS24 10.5 OZ Striped Shortbread Packaging Traypacker Shutdown\Changeover 283 243.38 STARK, DALE L. 3

[dilipandey]

Hi Kurt,

Could you post the sample file containing the data you shared and the pivot macro. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Kurt Lyons]

Here is what I have so far. It errors out when it gets to creating the pivot table.

Sub Macro1()
'
' Macro1 Macro
'

'
'Select data from the scrap data sheet.
Sheets("Scrap Data").Select
Range("B1:M29237").Select


' Creat pivot table from selected data
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Range("C1:M29237"), Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Scrap Analysis!R2C2", TableName:="PivotTable3", _
DefaultVersion:=xlPivotTableVersion12

'Set up pivot table to sort against Item looking at Weight by Unit Description
Sheets("Scrap Analysis").Select
Cells(1, 1).Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("PF Item")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit Description")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Weight")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PF Item")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Weight"), "Count of Weight", xlCount
Range("B4").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Weight")
.Caption = "Sum of Weight"
.Function = xlSum
End With

'Change Unit Description weight from count to Sum of Weight
Range("A5:B11").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit Description"). _
AutoSort xlDescending, "Sum of Weight"

'Create the Chart
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Scrap Analysis'!$A$4:$B$12")
ActiveWorkbook.ShowPivotChartActiveFields = True
ActiveChart.ChartType = xlBarClustered
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit Description")
.PivotItems("(blank)").Visible = False
End With
Range("A5:B10").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit Description"). _
AutoSort xlAscending, "Sum of Weight"
ActiveWorkbook.ShowPivotTableFieldList = False
Sheets("Scrap Data").Select
ActiveWorkbook.Worksheets("Scrap Analysis").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Scrap Analysis!R1C12", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion12
Sheets("Scrap Analysis").Select
Cells(1, 12).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("PF Item")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reason Description")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Weight")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("PF Item")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Weight"), "Count of Weight", xlCount

Range("L5").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Scrap Analysis'!$L$4:$M$43")
ActiveChart.ChartType = xlBarClustered
Range("M4").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Weight")
.Caption = "Sum of Weight"
.Function = xlSum
End With
Range("L5:M42").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Reason Description"). _
AutoSort xlAscending, "Sum of Weight"

ActiveWorkbook.ShowPivotTableFieldList = False
Range("Y8").Select

End Sub

[dilipandey]

Hi Kurt,

Please post the excel workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[florence_]

How want to create macros in excel 2007 i don't know how to coding for this macros. Can you please explain

[arlu1201]

Florence,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.