CODEWITHSUNDEEP
vba
Bale Rudal
Bale Rudal

Reputation: 69

Getting Chart Value from Active Sheet rather than Specific Sheet

I am working on plotting a chart on several sheets. Initially, I use macro-recording but it only assigns a specific sheet without able to obtain the data on every sheet.

Here is the code

ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select
    ActiveChart.SetSourceData Source:=Range( _
        "Sheet12!$A$20:$A$54,Sheet12!$C$20:$D$54")

I tried to assign using the active worksheet as follow:

ActiveChart.SetSourceData Source:=Range( _
        "ActiveWorksheet!$A$20:$A$54,ActiveWorksheet!$C$20:$D$54")

But unfortunately, it didn't work. Could you help me to fix the code to work with the active worksheet so I can work on several sheets? Thank you in advance

Upvotes: 1

Views: 601

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6053

A slightly more robust solution would be:

ActiveChart.SetSourceData Source:=ActiveSheet.Range( _
    ActiveSheet.Range("$A$20:$A$54"),ActiveSheet.Range("$C$20:$D$54"))

Upvotes: 0

ed2
ed2

Reputation: 1497

I have two ideas. Both untested.

A. You could try removing references to the specific sheet (which you have replaced with activesheet) in the range definitions, like below.

ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth).Select
    ActiveChart.SetSourceData Source:=Range( _
        "$A$20:$A$54,$C$20:$D$54")

This is because "ActiveSheet" is VBA syntax, not syntax that excel chart settings will recognise.

B. If that doesn't work, you may need a few more steps in your code. For example:

  1. Create a chart (you have coded this already), but as a variable expression so that you can then adjust its settings, and pass the sheet name through a variable to use within the range. So:
Dim oNewChart As ChartObject
Dim sCurrentSheetName$, sSourceRange$, sFullRange$

sSourceRange = "$A$20:$A$54,$C$20:$D$54"

sCurrentSheetName = ActiveSheet.Name
sFullRange = sCurrentSheetName & "!" & sSourceRange
Set oNewChart = ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmooth)
oNewChart.Chart.SetSourceData Source:=sFullRange

I have not tested the above code. If it errors on the last line, try removing the ".Chart" from the middle of that line.

  1. If you are looping through multiple sheets to do this, you could also consider whether the list of sheets or the source range will be known in advance; if not, they can be passed in to the code from an overall handler that looks up the sheets in the current workbook. In that case you would need to replace the second instance of ActiveSheet in the code above to Sheets(sCurrentSheetName)

Upvotes: 1

Related Questions