ActiveSheet.Name for Chart in VB

[davidhall80]

Is it possible to change the "Sheet1" in my code to whatever the active sheet name is. I'm producing charts for several tabs and each has a different tab name. Any recommendations. Also any recommendations on simplifying this code. Thanks guys!!...



Set chart = Range("C8:E8").Resize(Range("C" & Rows.Count).End(xlUp).Row - 7)
Range(chart, chart).Select
Charts.Add
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(chart, chart), PlotBy :=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"..

 

[VoG]

Just change

Sheets("Sheet1")

to

ActiveSheet

 

[davidhall80]

What about this part??

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

 

[VoG]

Sorry, missed that one: try

 ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name

 

[davidhall80]

Also replacing (Sheets("Sheet1") give me an "Object doesn't support this object or method" error

 

[davidhall80]

OK...I'll give that a shot

 

[davidhall80]

Still not working...Maybe I should temporarily name each sheet the same name while creating the chart and than rename it...What do you think??

 

[Jon Peltier]

 ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name

The active sheet is the chart you're trying to move to the previous active sheet.

You can either assign a variable to the active sheet before inserting the chart, for use later:

Dim sShtName as String
sShtName = ActiveSheet.Name
' ...
ActiveChart.Location Where:=xlLocationAsObject, Name:=sShtName

or you could create the chart directly as a chart object in the active sheet using ActiveSheet.ChartObjects.Add
I have some examples on my web site:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

 

[davidhall80]

Thanks.I'll give it a shot

 

[LNG2013]

Jon I know this thread is from 2007 but I just wanted to say thank you. I have been trying to add a pie chart for the last week or two and have been having similar troubles like David.

Your solution fixed it FLAWLESSLY! Awesome!