Help with VBA coding for Excel Pivot Table

[jupops]

Good Morning All

Can anybody help?

I am trying to write a Macro that will create a pivot table that will count up and total various columns.(which will have the same headings, but the data will always be different).

I have recoded a Macro and than tried to write it manually, but they keep failing at the same point (runtime error 1004) when trying to add the Items to the row field.

Can you see where I am going wrong, and if possible suggest a solution. Thank you in advance.

Below are both versions of the Macro:

Recorded Macro

Sub Macro1()
'
'
Columns("A:C").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Calc!C1:C3").CreatePivotTable TableDestination:= _
"'[Letdowns Not Needed.xls]Item'!R1C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Items"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Items").Orientation = _
xlDataField
End Sub

Manually Written Macro

Sub Item_Report()
'
' Item_Report Macro
'

Dim PTCache As PivotCache
Dim PT As PivotTable

Sheets("Calc").Select
Columns("A:C").Select

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:="Calc!C1:C3")

Set PT = PTCache.CreatePivotTable _
(TableDestination:="'[Letdowns Not Needed.xls]Item'!R1C1", _
TableName:="ItemsPivotTab")

With PT
.PivotFields("Items").Orientation = RowFields
.PivotFields("Items").Orientation = xlDataField



End With

End Sub

 

[SkipVought]

Hi,

Have you tried creating the PT using the PT Wizard?

If so, have you tried using your macro recorder to record the process?

What is the reason you want to create the PT via code?

Please answer all questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rofeu]

Hi,

is your sourcerange okay? (C1:C3 seems to hardly merit a pivottable)

try:

.PivotFields("Items").Orientation = [u][b]xl[/b][/u]RowFields

but even better is to replace it with:

.AddDataField .PivotFields("Items"), "Sum of Item", xlSum

Cheers,

Roel