Recording a Macro to create a Pivot Table
 

I am trying to create a macro which will open a CSV file - save it as a
spreadsheet and then create a pivot table. I have recorded a macro but I
continue to get a runtime error of "1004 - PivotTableWizard method of
Worksheet class failed" I have no idea what I'm doing wrong - any help much
appreciated.
This is what the data is like

NDR Quarter
6400 2006Q3
6400 2006Q3
16422 2006Q3
14450 2006Q3
48000 2006Q3
6427 2006Q3
-19200 2006Q4
19200 2006Q4
32000 2006Q4
23750 2006Q4
34000 2006Q4

And here is the code
Private Sub Workbook_Open()
Dim Pt As PivotTable
'
' Macro6 Macro
'

'
Workbooks.Open Filename:="c:\q_ndr.csv"
ActiveWorkbook.SaveAs Filename:="C:\q_ndr1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"q_ndr!R1C1:R12C2").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable7", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Range("A3").Select
ActiveSheet.PivotTables("PivotTable7").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("NDR"), "Sum of NDR", xlSum
With ActiveSheet.PivotTables("PivotTable7").PivotFields ("Quarter")
.Orientation = xlRowField
.Position = 1
End With
Range("A5").Select
End Sub

Recording a Macro to create a Pivot Table
 

I believe you need to delete the old PT first before creating a new one in
the same place. It may be easier to just create the PT once manually and use
it as a template for importing new data from CSVs to the same PT, and then
Refresh and SAVEAS.

Mike F
"Sandi B" <Sandi wrote in message
...
I am trying to create a macro which will open a CSV file - save it as a
spreadsheet and then create a pivot table. I have recorded a macro but I
continue to get a runtime error of "1004 - PivotTableWizard method of
Worksheet class failed" I have no idea what I'm doing wrong - any help
much
appreciated.
This is what the data is like

NDR Quarter
6400 2006Q3
6400 2006Q3
16422 2006Q3
14450 2006Q3
48000 2006Q3
6427 2006Q3
-19200 2006Q4
19200 2006Q4
32000 2006Q4
23750 2006Q4
34000 2006Q4

And here is the code
Private Sub Workbook_Open()
Dim Pt As PivotTable
'
' Macro6 Macro
'

'
Workbooks.Open Filename:="c:\q_ndr.csv"
ActiveWorkbook.SaveAs Filename:="C:\q_ndr1.xls", FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"q_ndr!R1C1:R12C2").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable7", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Range("A3").Select
ActiveSheet.PivotTables("PivotTable7").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("NDR"), "Sum of NDR", xlSum
With ActiveSheet.PivotTables("PivotTable7").PivotFields ("Quarter")
.Orientation = xlRowField
.Position = 1
End With
Range("A5").Select
End Sub