Name a Worksheet Based on a Value in a Cell

[bridgeport]

I have a workbook containing 25 worksheets. i would like for each worksheet to be named based on what is in cell S6. Does anyone know how I can do this? I might add that S6 has a vlookup in it that pulls in its value....Please help. Thanks!

[RobWulf]

I do it this way in one of my projects
Normally I do this from a form, but this should work.

Just reread your post, you would have to loop through each sheet and look at the same cell location.

Set ws = Worksheets("Schedule")

sName= ws.Cells(iRow, 1)

ActiveSheet.Name = sName

[bridgeport]

Im not really sure what you mean that I need to do...sorry, I am not very good at this stuff...

Jo

[JOHN H. DAVIS]

Maybe:

Sub bridgeport()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.Name = Range("S6").Value
Next ws
End Sub

[Richard Buttrey]

Hi,

What RobWulf is saying is that unless you do it manually you will need a macro to do it for you. Are you familiar with macro code and where it sits.

Perhaps the basic question is are the sheet names going to be changing continually when S6 changes? If not I'd be inclined to bite the bullet and just rename them manually. That will be quicker than implementing a macro if you're not yet familiar with them.

[bridgeport]

Yes, the value in S6 is always going to change....this is going to be used for approx. 600 worksheets all together. i think I need to create a macro, but need guidance as to how to do it....can you help?

[bridgeport]

The below sentence hilighted when i ran this....So you know what I might have done wrong?

Thanks! Jo

ActiveSheet.Name = Range("S6").Value

[JOHN H. DAVIS]

Can you post the code you ran? I tested my suggestion and it worked for me.

[bridgeport]

Can I email you the file? don't really fee comfortable posting it..??

[JOHN H. DAVIS]

I only want to look at the code. Only to check that it is the same as I posted and what may be causing the error. I don't think there would be any confidentiallity involved there? Not sure.

[bridgeport]

When I put the below code in , I get a Microsoft Visual Basic box pop up that says

Run-time error '1004':
Application-defined or object-defined error, then I have the option to end, debug, or help.

Sub bridgeport()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveSheet.Name = Range("S6").Value
Next ws
End Sub

[JOHN H. DAVIS]

Try:

Sub bridgeport()
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.Name = ws.Range("S6").Value
Next ws
On Error GoTo 0
End Sub

[oeldere]

You have to use tags (#) on your code.

I think you don't have a value in S6.

This could cause the trouble.

[bridgeport]

Okay, so this is working on all but the last two sheets in the workbook...do you know what I could be doing wrong? Please help....we are sooo close!! Thanks!

[oeldere]

Maybe you could reply on the answer in #12.