Hi there

Trying to automate pivot tables and associated pivot charts. I know this can easily been done for repetitive processes to recreate pivots when data is updated, but how would it work for a totally new set of data where data table column headers are not known yet? BTW very new to VBA and actually WRITING macros....

eg this is code I found online:

Sub CreatePivot()
' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField

' Select the sheet and first cell of the table that contains the data.

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTableWizard

' Specify row and column fields.
Set objField = objTable.PivotFields("DEPT")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("LOCATION")
objField.Orientation = xlColumnField

' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("SALARY")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"

' Specify a page field.
Set objField = objTable.PivotFields("GENDER")
objField.Orientation = xlPageField

' Preview the new PivotTable report.

' Prompt the user whether to delete the PivotTable.
Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
End If
Application.DisplayAlerts = True
End Sub

Now, these refer to SPECIFIC data table column header names (eg bjTable.PivotFields("DEPT")

I was therefore wondering how this would work if for instance the data table headers were named DC1, DC2, DC3 etc. It will be at the stage that a user starts entering data that they also will rename the headers to identify the data clearly for their purposes.

How can the above code be amended so that it can work when the SPECIFIC data column field headers are not know yet at time of macro/code creation?

Really stumped...