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 Whe=xlLocationAsObject, Name:=ActiveSheet

to

ActiveChart.Location Whe=xlLocationAsObject, Name:=myShtName

HTH,
Bernie
MS Excel MVP


"wussupbuddy" wrote in message
...

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("B23:D24"), PlotBy:= _
xlRows
ActiveChart.Location Whe=xlLocationAsObject, Name:=ActiveSheet
End Sub


--
wussupbuddy
------------------------------------------------------------------------
wussupbuddy's Profile: http://www.excelforum.com/member.php...o&userid=32107
View this thread: http://www.excelforum.com/showthread...hreadid=518650