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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB To Re-Name Worksheet Name
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB To Re-Name Worksheet Name
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" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB To Re-Name Worksheet Name
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. Tom Ogilvy wrote: 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 |
Reply |
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 |