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").AddDataFiel d
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

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("Organizat ion")
.Orientation = xlRowField
.Position = 1
End With
.PivotTables("PivotTable2").AddDataField
.PivotTables("PivotTable2").PivotFields(FieldName) , _
"Sum of Total Project Cost", _
xlSum
end with


"OMER" wrote:

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").AddDataFiel d
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

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

"Joel" wrote:

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("Organizat ion")
.Orientation = xlRowField
.Position = 1
End With
.PivotTables("PivotTable2").AddDataField
.PivotTables("PivotTable2").PivotFields(FieldName) , _
"Sum of Total Project Cost", _
xlSum
end with


"OMER" wrote:

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").AddDataFiel d
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

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

"Joel" wrote:

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("Organizat ion")
.Orientation = xlRowField
.Position = 1
End With
.PivotTables("PivotTable2").AddDataField
.PivotTables("PivotTable2").PivotFields(FieldName) , _
"Sum of Total Project Cost", _
xlSum
end with


"OMER" wrote:

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").AddDataFiel d
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