Variable column names and ranges in Pivot Tables within macros.

[OMER]

Hola,
I crated this code using the Macro Recorder. The issue that I have is that
there are some fixed names within the code that should be variable, depending
on the source table column names. Is there a way to substitute ranges and
field names for variables?

Here is the relevant code:

ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Inv_Graph'!$A$1:$C$18")
ActiveWorkbook.ShowPivotChartActiveFields = True
ActiveChart.ChartType = xlColumnClustered
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Organization")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Total Project Cost"), "Sum of Total
Project Cost", _
xlSum


For example, source data doesn't always has the column header as "Total
Project Cost", or "Total Resource"

Thank you in advance.
OMER

 

[Joel]

Anything between double quotes are strings and can be set to a variable like
I did below. Have you tried running you recorded script. Does it work?
there are some inconsistancy in recorded macros that they don't always work
after being recorded. Especially with charts. A new chart actually gets
created as its own sheet and then gets then if you choose it gets placed onto
a worksheet. The chart name changes when you place it on an existing
worksheet. I don't think your code will run, but I may be wrong.

with Activeworkbook
fieldName = "Total Project Cost"
Set sourcerange = Sheets("Inv_Graph").range
.ShowPivotTableFieldList = True
ActiveSheet.Shapes.AddChart.Select
Set Newchart = Activechart
NewChart.SetSourceData Source:=sourcerange
.ShowPivotChartActiveFields = True
Newchart.ChartType = xlColumnClustered
With .PivotTables("PivotTable2").PivotFields("Organization")
.Orientation = xlRowField
.Position = 1
End With
.PivotTables("PivotTable2").AddDataField
.PivotTables("PivotTable2").PivotFields(FieldName), _
"Sum of Total Project Cost", _
xlSum
end with

 

[OMER]

Thank You Joel, The solution looks simple enough (I'm not that an expert in
VBA) , The entire macro works fine, I just wanted to make it more flexible so
different set of column names and rows work without user intervention.
I'll try this and let you know the result.

Regards,
OMER

 

[OMER]

I made the suggested changes and they worked fine. Thank You very much Joel.
Regards,
OMER