I import data into Excel 2007 regularly and create a pivot table with several columns. I have been copying the sheet in to a template spreadsheet, refreshing the data and then copying the updated pivot table data back to the original spreadsheet. I would like to learn how to create a macro that creates the pivot table for me. I’ve looked at several examples on the forum, but it is hard to see what has been done. I create macros regularly, but only by using the record macro feature in Excel. When I did this with my data, I got an error immediately after a new sheet was created. The part that was highlighted in yellow in VBA below it is the line starting with "ActiveWorkbook". My columns heading will be static, but the number of rows will change. I want to create a simple pivot table with columns G though Z.

Sub Macro3()
'
' Macro3 Macro
'

'
Columns("M:V").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C13:R1048576C22", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet9!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet9").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Date of Loss")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("$ Res"), "Count of $ Res", xlCount
End Sub