Vba Graphs/ Charts with Varrying Sheets

[finaljustice]

Hi!
I've been trying to generate a macro which automatically makes charts and graphs. I have been successfull to do this if I write it specifically a code for each Sheet I have, I figure there must be a way to code it to make it a generic code for all sheets.

My issue comes on this line:

ActiveChart.SetSourceData Source:=Range("'ZM1'!"$O$1,$AA$1,$AM$1,$BB$1,$BK$1,$BW$1,$CM$1,$CU$1,$DG$1"")

What I wonder if it can be done is if I can put a variable as the sheet when ordering it to get that data source. What will change will be the names of my sheets so "ZM1","ZM2", "ZM3".

What I tried unsuccessfully was:

x = ActiveSheet.Name
ActiveChart.SetSourceData Source:=Range("'x'!"$O$1,$AA$1,$AM$1,$BB$1,$BK$1,$BW$1,$CM$1,$CU$1,$DG$1"")

but VBA won't recognise X as a variable, that has the name of my desired sheet.


Any ideas?
Thank you for you attention.
Final

 

[daverunt]

Try

x = ActiveSheet.Name 
Source:=Range("'" & x & "'" & "!$O$1........................

 

[finaljustice]

Try

x = ActiveSheet.Name 
Source:=Range("'" & x & "'" & "!$O$1........................

Thank you for your response, I actually ended up coming up with the same solution but thank you once again.