Refering to the active worksheet in a macro

[RAGTIME333]

Hi I am new to VB and recorded a macro to update a number of graphs in a number of worksheets with additional data. This is what i recorded

Sub EDITCHART2()
'
' EDITCHART2 Macro
' Macro recorded 6/23/2009 by mcarag
'
' Keyboard Shortcut: Ctrl+Shift+W
'

ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=(Q11.5!R11C2:R15C2,Q11.5!R17C2)"
ActiveChart.SeriesCollection(1).Values = "=(Q11.5!R11C3:R15C3,Q11.5!R17C3)"
ActiveChart.SeriesCollection(2).XValues = "=(Q11.5!R11C2:R15C2,Q11.5!R17C2)"
ActiveChart.SeriesCollection(2).Values = "=(Q11.5!R11C4:R15C4,Q11.5!R17C4)"
ActiveChart.SeriesCollection(3).XValues = "=(Q11.5!R11C2:R15C2,Q11.5!R17C2)"
ActiveChart.SeriesCollection(3).Values = "=(Q11.5!R11C5:R15C5,Q11.5!R17C5)"
End Sub

However I want the macro to reference each worksheet I run it in, rather than Q11.5 each time. Simple I'm sure and let here I sit...

 

[PCL]

NOT TESTED

MySheet = Activesheet.name
ActiveChart.SeriesCollection(1).XValues = "=(" & MySheet & "!R11C2:R15C2," & MySheet &"!R17C2)"

 

[RAGTIME333]

I feel like you are on the right track with the & symbols but it don't work it gives me an unexpected end of statement error

 

[PCL]

Perhaps couild you add an extra variable

AAA = "=(" & MySheet & "!R11C2:R15C2," & MySheet &"!R17C2)"<!-- / message -->

and check if the value is correct, means it looks like the previous value

"=(Q11.5!R11C2:R15C2,Q11.5!R17C2)"

Does variable MySheet has the right value.

 

[FlammConsulting]

MySheet=ActiveSheet.name? That assigns a string value (the name of the Active Sheet, eg, "Sheet1") to MySheet. If you want MySheet to represent a sheet object, you need to use:

Dim MySheet as Worksheet
Set MySheet=ActiveSheet

However, I don't know why you need to do that - just use ActiveSheet.Range(range spec) - this will refer the referenced range on the active sheet (that is, the sheet where the currently selected cell resides).

For example, you could use

ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("Q11.5!R11C2:R15C2,Q11.5!R17C2")

although I'm not sure I understand the syntax of that range specification (I don't do a lot of charting from VBA).

HTH,

EricF