Method 'Location' of object' _Chart failed

Hi,

the above run time error occurs when I run the macro for the first time after opening the spreadsheet, normally I just click "End" and run the macro again and it works fine. Thought that I better fix the problem. part of the macro is below:

Application.DisplayAlerts = False
Sheets("Chart1").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Range("AF5:AG25").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Data").Range("AF5:AG25"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Scanned"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart1"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale


the line that causes the error is

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart1"

but as I said only the first time running after opening

Could someone please tell me what I am doing wrong. Many thanks

Hi Eagle44, welcome to the forum! :)

First off, it's always a good idea to fully qualify your references (i.e. using Book.Sheets instead of just Sheets, where Book is the codename of your workbook, typically ThisWorkbook). Also, the Active... references should only be used when necessary, in your case you don't need any. ;)
Unfortunately, code produces by the macro recorder always contains tons of them. Your code can be streamlined as follows:
Application.DisplayAlerts = False
Book.Sheets("Chart1").Delete
Application.DisplayAlerts = True

With Book.Charts.Add
.ChartType = xlColumnClustered
.SetSourceData Source:=Book.Sheets("Data").Range("AF5:AG25"), PlotBy:=xlColumns
.SeriesCollection(1).Name = "=""Scanned"""
.Location Where:=xlLocationAsNewSheet, Name:="Chart1"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
End With

Now, none of this fixes your problem. But I think you can work out what causes it for yourself: try changing "Chart1" in your code to "Chart2" or "Chart3" and run it a few times, that'll give you a clue.

Hi Cas,

Thanks for the help will give it a try, unfortuantely I do rely heavily on recorded macros then tweak them as required as most of what I am required to do is create quick and dirty macros for people.

Cheers

If you had meant to make me assign the chart sheet a name rather that use the Excel default then that is what I did and it works great.

Thanks very much for the help

Cheers