Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB To Re-Name Worksheet Name
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |