rename worksheet by cell value

[trancer23]

can i write a macro to rename a worksheet by a cell value

i have a macro that generates lots of charts and i would like to name them based on a cell value

thanx
jeff

 

[btadams]

add a line to your code such as

Activesheet.Name = Range("A1").Value

 

[trancer23]

cool thanx, unfortunately b/c its in a loop i need something along the lines of

ActiveSheet.Name = Cells(1, startcol).Value

but that doesnt work.. startcol is column variable

thanx
jeff

 

[btadams]

I'd really need to see your workbook and the macro you've created to see what it is you are really trying to accomplish but you could create a variable for startcol and increment it within your loop:

Dim StartCol as integer

' Your loop
Activesheet.Name = Cells(1, StartCol).Value
StartCol = StartCol + 1
' Loop

 

[Albert 1]

Don't forget to give Startcol a value to start with, (e.g.):
Startcol = 1

 

[trancer23]

here is my macro:

Worksheets("Sheet1").Activate
startcol = 1
For profile = 1 To 240
Range(Cells(1, startcol), Cells(400, startcol + 1)).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.Location Where:=xlLocationAsNewSheet
Sheets("Sheet1").Select
startcol = startcol + 2
profile = profile + 1
Next profile


why sheet names are in row one1, every other one, hence the startcol +2 counter

thanx

 

[btadams]

try:

Worksheets("Sheet1").Activate
startcol = 1
For profile = 1 To 240
Range(Cells(1, startcol), Cells(400, startcol + 1)).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.Location Where:=xlLocationAsNewSheet
Activesheet.Name = Range(Cells(1, startcol)).Value
Sheets("Sheet1").Select
startcol = startcol + 2
profile = profile + 1
Next profile

 

[trancer23]

still fails on the renaming line

Run-time error '1004':

Method 'Range" of object '_Global" Failed

any ideas?

 

[btadams]

TRY THIS:

Worksheets("Sheet1").Activate
startcol = 1
For profile = 1 To 240
Range(Cells(1, startcol), Cells(400, startcol + 1)).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.Location Where:=xlLocationAsNewSheet
Activesheet.Name = Sheets("Sheet1").Cells(1, startcol).Value
Sheets("Sheet1").Select
startcol = startcol + 2
profile = profile + 1
Next profile

 

[trancer23]

btadams you ROCK!!!!!

thanx so much.. that worked

-jeff