ActiveSheet.Name for Chart in VB

D

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
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"..
 
Sort by date Sort by votes
Sorry, missed that one: try

Code: 
 ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
 
Upvote 0
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??
 
Upvote 0
Code: 
 ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
Click to expand...
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:
Code: 
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
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Create a Dynamic Line Chart for last 4 weeks of data
Replies
2
Views
250
StephenCrump
S
A
  • Question Question
VBA problem with setting data source range
Replies
1
Views
1K
Akbarov
A
L
  • Solved
VBA Help Modify Code - Prevent chart from resizing with columns / rows
Replies
3
Views
1K
Anthony47
A
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
4K
profray
P
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
612
offthelip
O

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top