rename worksheet by cell value

btadams

Well-known Member

Joined

Jan 6, 2003

Messages

1,943

• Apr 14, 2003

• #2

add a line to your code such as

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

 

Upvote 0

T

trancer23

New Member

Joined

Apr 14, 2003

Messages

11

• Apr 14, 2003

• #3

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

 

Upvote 0

btadams

Well-known Member

Joined

Jan 6, 2003

Messages

1,943

• Apr 14, 2003

• #4

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

 

Upvote 0

A

Albert 1

Active Member

Joined

Feb 22, 2002

Messages

395

• Apr 14, 2003

• #5

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

 

Upvote 0

T

trancer23

New Member

Joined

Apr 14, 2003

Messages

11

• Apr 14, 2003

• #6

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

 

Upvote 0

btadams

Well-known Member

Joined

Jan 6, 2003

Messages

1,943

• Apr 14, 2003

• #7

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

 

Upvote 0

T

trancer23

New Member

Joined

Apr 14, 2003

Messages

11

• Apr 15, 2003

• #8

still fails on the renaming line

Run-time error '1004':

Method 'Range" of object '_Global" Failed

any ideas?

 

Upvote 0

btadams

Well-known Member

Joined

Jan 6, 2003

Messages

1,943

• Apr 15, 2003

• #9

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

 

Upvote 0

T

trancer23

New Member

Joined

Apr 14, 2003

Messages

11

• Apr 15, 2003

• #10

btadams you ROCK!!!!!

thanx so much.. that worked

-jeff

 

Upvote 0