Automating Pivot table and pivot chart creation if data table names unknown
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:
' 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
Application.DisplayAlerts = True
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?
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By PWM in forum Excel Charting & Pivots
Last Post: 03-24-2016, 01:49 PM
By TomOF15 in forum Excel Charting & Pivots
Last Post: 07-22-2015, 07:16 AM
By BigJack in forum Excel Programming / VBA / Macros
Last Post: 02-21-2014, 08:31 AM
By Xcelguy in forum Excel Charting & Pivots
Last Post: 05-24-2013, 03:58 PM
By LeapingLizard in forum Excel Programming / VBA / Macros
Last Post: 02-01-2013, 06:19 PM
By questionguy in forum Excel General
Last Post: 04-16-2012, 05:06 PM
By excelkeechak in forum Excel General
Last Post: 02-27-2010, 11:56 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1