Hi,

I'm currently trying to construct a Pivot Table but the range to use depends
on the location of a certain column header within the sheet (column changes
each time, always the same row). Currently I have a series of 15 If
statements for the various columns the value could be in but I'm sure there
must be an easier way to do this using the "Find" function. (I've put the
current VBA code at the bottom of this message).

Any suggestions as to how this could be improved?

Thanks in advance!! Jen




If Range("J4") = "Week of campaign" Then
'CONSTRUCT THE PIVOT TABLE
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Data!R4C5:R25000C10").CreatePivotTable TableDestination:="Pivot3!R1C1",
TableName:= _
"WeekMod", DefaultVersion:=xlPivotTableVersion10

Sheets("Pivot3").Select
With ActiveSheet.PivotTables("WeekMod").PivotFields("We ek of
campaign")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("WeekMod").AddDataField
ActiveSheet.PivotTables("WeekMod" _
).PivotFields("Model"), "Count of Model", xlCount
With ActiveSheet.PivotTables("WeekMod").PivotFields("Mo del")
.Orientation = xlColumnField
.Position = 1
End With
End If