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

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

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

--
wussupbudd
-----------------------------------------------------------------------
wussupbuddy's Profile: http://www.excelforum.com/member.php...fo&userid=3210
View this thread: http://www.excelforum.com/showthread.php?threadid=51865

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" wrote in message
...

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


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

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

--
wussupbudd
-----------------------------------------------------------------------
wussupbuddy's Profile: http://www.excelforum.com/member.php...fo&userid=3210
View this thread: http://www.excelforum.com/showthread.php?threadid=51865

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" wrote in message
...

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


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

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...

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


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

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


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