VBA - Pivot Tables - Populate Pivot with Changing column headers

[fife8]

Hello/Thanks In Advance.

I am trying to find a way to loop thru column headers so that I can use what is found in VBA code to sort into a Pivot Table.

Column headers could be a number of Age combinations:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]P 18-24 Boys[/TD]
[TD]P 25-54 Boys[/TD]
[/TR]
</tbody>[/TABLE]

For example, I want to take any column header that contains text *Boys* - and do the below for me:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("P 18-24 Boys")
.Orientation = xlPageField
.Position = 1
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("P 25-54 Boys")
.Orientation = xlPageField
.Position = 1
End With


Thanks
Cathy

 

[fife8]

Have answer -

' FIND THE VALUES AND PUT INTO SUM BOX
Set ws = ActiveWorkbook.Sheets("Sheet1")
lngCurrentCol = 1
blnFound = False
Do While ws.Cells(1, lngCurrentCol) <> ""


If InStr(ws.Cells(1, lngCurrentCol), "Boys") > 0 Then
blnFound = True
strText = ws.Cells(1, lngCurrentCol)
End If

If blnFound = True Then
blnFound = False
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(strText), "Count of " & strText, xlCount

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of " & strText)
.Caption = "Sum of " & strText
.Function = xlSum
End With
End If


lngCurrentCol = lngCurrentCol + 1
Loop