Graph Data Range change based on current month

[raj08536]

Hello

I have recorded a macro to change the Select Data

Sub TestMacro2()
'
' Macro2 Macro
'

'
ActiveSheet.ChartObjects("Chart 35").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("A142:E150")
End Sub

I am now trying to automate to pick up cell values to change the range of data. Please check

Sub ChrtRngChg_EP()

Dim CM As Variant
Dim RNGColB_EP As Variant
Dim RNGRowB_EP As Variant
Dim RNGColE_EP As Variant
Dim RNGRowE_EP As Variant


CM = Sheets("Parameters").Cells(5, 2).Value
RNGColB_EP = Sheets("Parameters").Cells(2, 16).Value
RNGRowB_EP = Sheets("Parameters").Cells(2, 18).Value
RNGColE_EP = Sheets("Parameters").Cells(2, 17).Value
RNGRowE_EP = Sheets("Parameters").Cells(2, 19).Value



Sheets("Eight Pillars").Select

ActiveSheet.ChartObjects("Chart 35").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:="=Range(" & RNGColB_EP & RNGRowB_EP & ":" & RNGColE_EP & RNGColE_EP & ")"



End Sub

It's give me error is the last line

Please help.

 

[Crystalyzer]

It feels like you are cutting fruit salad with a chainsaw. Couldn't you use dynamic range names for your data source? there are plenty of examples of this everywhere. Here is one:

How to Create a Dynamic Chart Range in Excel

 

[raj08536]

I appreciate your suggestion.
This particular excel file has 50 tabs and each tab has atleast 15 graphs. I can't create 750 tables and named range. Therefore, I want to write a vba for updating range for charts.