VBA Error - Creating Pivot table - Please Help

[fife8]

I have the following code - I am bombing at the bold part below. Runtime Error 1004 - Pivot table field name is not valid. ....

Range("A1").Select
Cells.Select
ActiveSheet.Name = "Spend Data"
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Spend Data!R1C1:R1048576C20", Version:= _
xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MARKET")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TIME PERIOD")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ADVERTISER")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("DOLS (000)"), "Count of DOLS (000)", xlCount
End Sub

 

[fife8]

IT defaults to

"Spend Data!R1C1:R1048576C7", Version:= _

Because I have total of 7 columns (C7 at end) - I tried to expand that to allow for growth. It doesnt like that.
Can anyone think of a way to make the number of columns dynamic?