Charts.Add
Activechart.name = worksheets("sheet2").Cells(n-2,1).Value


seems funny to get your data from row n+2 and your name from n-2, but that
is what your code does and where you said you wanted to retrieve the name.

--
Regards,
Tom Ogilvy


"mate" wrote:

Hi

I have the following code to add a new chart for each row of data on
sheet 2 but I would like to name each worksheet with the value
(text/name) from sheet 2 cell A1, Sheet 2 cell A2 etc.
Ie A1 = Mr Smith therefore chart 1 tab name needs to = Mr Smith
A2 = Mr Jones - chart 2 tab name = Mr Jones


Dim cell1 As String
Dim cell2 As String
Dim cell3 As String
Dim n As Variant
For n = 3 To 200

cell1 = "a" & n + 2
cell2 = "h" & n + 2
cell3 = "b" & n + 2
If Sheets("Sheet1").Cells(n, 10) = 0 Then Exit For

Charts.Add
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(cell1,
cell2), PlotBy:= _
xlRows

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C8"
ActiveChart.SeriesCollection(1).Values = "=Sheet2!R4C2:R4C8"
ActiveChart.SeriesCollection(1).Name = "=Sheet2!R4C1"
ActiveChart.SeriesCollection(2).Values =
Sheets("Sheet2").Range(cell3, cell2)
ActiveChart.SeriesCollection(2).Name = Sheets("Sheet1").Cells(n, 1)
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheets("Sheet1").Cells(1, 1) & "
Advising Clinician: " & Sheets("Sheet1").Cells(n, 1) & "
Total Calls = " & Sheets("Sheet1").Cells(n, 10)

[Deleted chart formatting code for ease of posting]


I did not write this code as I am a complete novice when it comes to
VBA however I have edited it in places so please be gentle with any
answers!!

Many Thanks in advance.