I used column A to determine the number of rows that should be included in the
pivottable range. And I used Row 1 to determine the number of columns.
I assume that the headers are all nicely valid--no duplicates and no blanks,
right. And that you want Sum for each of those other fields.
Option Explicit
Sub testme()
Dim myRng As Range
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RowFieldArray() As String
Dim DataFieldArray() As String
Dim wks As Worksheet
Set wks = Worksheets("Sheet1")
With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 3 'skipping columns A and B
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myRng = .Range("A1", .Cells(LastRow, LastCol))
ReDim RowFieldArray(1 To 3)
RowFieldArray(1) = .Range("a1").Value
RowFieldArray(2) = .Range("b1").Value
RowFieldArray(3) = "Data"
ReDim DataFieldArray(3 To LastCol)
For iCol = 3 To LastCol
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol
.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRng.Address(external:=True)).CreatePivotTable _
TableDestination:="", _
TableName:="PT" & Format(Now, "yyyymmdd_hhmmss"), _
DefaultVersion:=xlPivotTableVersion10
End With
With ActiveSheet
.PivotTableWizard TableDestination:=.Range("A3")
.PivotTables(1).AddFields RowFields:=RowFieldArray
For iCol = 3 To LastCol
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Position = iCol - 2
.Function = xlSum
End With
Next iCol
With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
.UsedRange.Columns.AutoFit
.Range("a1").Select 'just to make sure it's visible
.Range("C5").Select
ActiveWindow.FreezePanes = True
End With
End Sub
It seemed to work ok for me in xl2003.