VB To Re-Name Worksheet Name

[mate]

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 Where:=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.

 

[Tom Ogilvy]

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.

 

[ChadF]

Hi 'mate' ...

Consider the following:

Toss these few lines into your sub please...

Dim aWS as Worksheet

Set aWS = ActiveWorksheet
' Set aWS = Sheets(2) ' set the worksheet to any worksheet in your
workbook.
' this line is commented out on
purpose


When you're at the point where you want to
rename your worksheet, insert the following...

ActiveWorkbook.Unprotect
aWS.name = Sheet2!A2 ' (or whatever cell you want) ... important
ActiveWorkbook.Protect ' thing to understand - aWS is a reference
' to the worksheet you want. So, you
can
' refer to its properties, giving it
a meaningful
' name.

' I put this statement in between the ActiveWorkbook Unprotect / Protect
' statements because changing the name of the worksheet requires the
' workbook itself to be unprotected. This set of statments makes no
assumptions
' about the state of the workbook. If it's overkill for what you're trying
to do, then
' dont include them.

Good luck,
Chad

 

[mate]

Thanks for the reply Tom, it was my mistake when typing - my name data
starts in A3 not A1.

The code as below works brilliantly when I adjusted to allow my my
initial error.
Activechart.name = worksheets("sheet2").Cells(n+2,1).Value

Thanks again.