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"..
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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

A
  • 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
948
Anthony47
A
E
  • 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
3K
profray
P
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
487
offthelip
O
D
  • Question
Setting chart columns
Replies
0
Views
345
domekker
D

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

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