Need help with chart

[wussupbuddy]

I'm trying to create a macro that would pull in data from the specified
range and embed the chart within the sheet from where the macro is
called. But the macro doesn't seem to be working. It gives me an error
after creating the chart in a different sheet. Can someone help me out
with this..

Sub final()
'
' final Macro
' Macro recorded 3/3/2006 by sjain
'

'
ActiveCell.Offset(-22, 0).Range("A1:G1").Select
ActiveCell.FormulaR1C1 = "Current Advisory"
ActiveCell.Offset(22, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Current Advisory"
ActiveCell.Offset(-22, 7).Range("A1:G1").Select
ActiveCell.FormulaR1C1 = "Advisor Histories"
ActiveCell.Offset(22, -6).Range("A1").Select
ActiveCell.FormulaR1C1 = "Advisor Histories"
ActiveCell.Offset(-22, 13).Range("A1:E1").Select
ActiveCell.FormulaR1C1 = "QA Reports"
ActiveCell.Offset(22, -12).Range("A1").Select
ActiveCell.FormulaR1C1 = "QA Reports"
ActiveCell.Offset(1, -2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VALUE(R[-4]C[5])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VALUE(R[-4]C[11])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=VALUE(R[-4]C[15])"
ActiveCell.Offset(-1, -2).Range("A1:C2").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData
Source:=Sheets(ActiveSheet).Range("B2324"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet
End Sub

 

[Bernie Deitrick]

When you add the chart, you no longer have an activesheet, since the chart is actice/ Therefore,
you need to do something like:

Dim myShtName As String

myShtName = Activesheet.Name

Then put your code here, but change

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

to

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

HTH,
Bernie
MS Excel MVP

 

[wussupbuddy]

thnx a ton..that worked..is there a way I can specify the co-ordinate
for the chart since the chart is coming on top of the data

 

[Bernie Deitrick]

Code like this will align the chart with H2:

ActiveSheet.Shapes("Chart 1").Left = Range("H2").Left
ActiveSheet.Shapes("Chart 1").Top = Range("H2").Top

You can also control the size like so:

ActiveSheet.Shapes("Chart 1").Width = Range("P2").Left - Range("H2").Left
ActiveSheet.Shapes("Chart 1").Top = Range("H10").Top - Range("H2").Top
ActiveSheet.Shapes("Chart 1").Left = Range("H2").Left
ActiveSheet.Shapes("Chart 1").Top = Range("H2").Top

HTH,
Bernie
MS Excel MVP

 

[wussupbuddy]

but how do I get to know the name of the chart that has just bee
created using the macro

 

[Bernie Deitrick]

I'm sorry. Use

Dim myName As String

myName = Replace(ActiveChart.Name, ActiveSheet.Name & " ", "")

ActiveSheet.Shapes(myName).IncrementLeft -147.75
ActiveSheet.Shapes(myName).IncrementTop 66

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Man, I'm just having a brain-dead kind of day:

Dim myName As String

myName = Replace(ActiveChart.Name, ActiveSheet.Name & " ", "")

ActiveSheet.Shapes(myName).Left = Range("H2").Left
ActiveSheet.Shapes(myName).Top = Range("H2").Top

HTH,
Bernie
MS Excel MVP

I'm sorry. Use

Dim myName As String

myName = Replace(ActiveChart.Name, ActiveSheet.Name & " ", "")

ActiveSheet.Shapes(myName).IncrementLeft -147.75
ActiveSheet.Shapes(myName).IncrementTop 66

HTH,
Bernie
MS Excel MVP

 

[wussupbuddy]

thnx a ton..that did the trick...