I am trying to create an embedded chart with a named dynamic range so that the range (and chart) will expand as new data is entered. The code runs fine with a a normal range.

I created the "Named Range" on the Inventory worksheet. The range is named "Vehicle_Count" with the following formula.

=OFFSET(Inventory!$D$55,0,0,COUNTA(Inventory!$D:$D),2)

The data for the range resides on the Inventory worksheet . C55 though C85 are the different names of the inventory (cars, trucks, buses. etc.) D55 through D85 holds the number of cars, trucks, buses, etc. in inventory.

The code below stops at .SetSourceData Source:=Vehicle_Count with an Object Required Error? The code is below.

Al you ideas would be appreciated. Named ranges, well heck just ranges, confuse me.

Thanks

Craig

--------------------
Sub AddEmbeddedChart(sToSheet As String, sTitle As String, sRng As Range, sUpper As String, sLeft As String, sWidth As String, sHeight As String, sName As String)
Dim chrtNew As Chart
Dim rngVehicles As Range

Sheets(sToSheet).Select

Set chrtNew = Charts.Add
Set chrtNew = chrtNew.Location(where:=xlLocationAsObject, Name:=sToSheet) 'Sheet where chart will be placed
With chrtNew
.ChartType = xlColumnClustered

.SetSourceData Source:=Vehicle_Count 'Dyanmic range not working
'Object Required error
.HasTitle = True
.ChartTitle.Text = sTitle
.Legend.Position = xlLegendPositionBottom
.HasLegend = False
.HasDataTable = False
.HasLegend = False
With .Parent
.Top = Range(sUpper).Top
.Left = Range(sLeft).Left
.Width = sWidth
.Height = sHeight
.RoundedCorners = True
.Shadow = True
.Visible = True
.Name = sName 'Name that gets activated for chart manipulation
End With
End With

ActiveChart.Deselect 'Removes all handles
End Sub