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:=xlDatabase, 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").AddDataField
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

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 [email protected]> wrote in message
news:[email protected]...
>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:=xlDatabase, 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").AddDataField
> 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
>