sheet tab name to reference cell data

[Regina J.]

I have a Workbook with several (16) sheets in it. I want the name of sheet 2
to reflect the info I have typed into cell B3 in sheet 1; sheet 3 to reflect
cell C3 in sheet 1, sheet 4 to reflect D3, etc. If I change the data in B3,
I want Sheet 2 to rename itself.

(Also, I use Excel 2000 - if that makes a difference)

I saw a question earlier that I thought answered this but can't figure out
how to do it. I don't know anything about macros or VBA so maybe I'm just
doing it wrong and not changing the right data in the formula. Please
explain in detail what formula I need, where to put it, and what data in the
formula I need to change to suit my individual project.

Thank you very much for any assistance I can get.

 

[Don Guillett]

Right click the sheet tab>view code>insert this. Now when you change a cell
in row 3 the appropriate sheet will be re-named accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row <> 3 Then Exit Sub
'MsgBox Target.Column
Sheets(Target.Column).Name = Target
End Sub

 

[Don Guillett]

Change to
Sheets(Target.Column-1).Name = Target

 

[Regina J.]

Nothing happened. I went to sheet 2, did the view code and that's where I
put it. Then I went back to sheet one and changed the data in B3 to check it
and nothing happened.
Also, I want to note that not all my data will be in row 3. Sheet 7 - 11
data will be in row 25, sheet 12 - 16 data wil be in row 47.

 

[Don Guillett]

I don't have xl2000 so don't know if that is the problem. I fully tested the
solution. Did you use the entire sub, as written?
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row <> 3 Then Exit Sub
'MsgBox Target.Column
Sheets(Target.Column-1).Name = Target
End Sub

BTW, You should ALWAYS state your problem (not just PART of it) in the OP. I
suspect you STILL have not fully stated your problem.

modify to suit
if target.row<>3 AND target.row<>
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row <> 3 And Target.Row <> 7 Then Exit Sub
'MsgBox Target.Column
Sheets(Target.Column - 1).Name = Target
End Sub

 

[Regina J.]

Yes I did use the entire sub as you had it typed even corrected it as you
stated in your follow-up.
BTW: You suspect WRONG! I DID state my FULL problem - wanting to name the
other sheet tabs from cells in sheet one - it should not matter what row they
are on! And as far as the lecture goes - why are you offering advice if you
don't have the program I am using anyway? I stated using Excel 2000 and you
replied with a solution. Don't offer help on things you don't know. We ask
for help because we need it from someone that can actually help not just hope
their idea will work.

 

[Don Guillett]

"Nothing happened. I went to sheet 2, did the view code and that's where I
put it. Then I went back to sheet one and changed"
It would have been helpful if you had put the code into the sheet module of
the sheet you are changing. Putting the code into sheet TWO has NO effect on
changes to sheet ONE....... You didn't go to school at Texas A & M, did you.

I went back and re-read your post several times. I see no mention of
anything but row THREE. I did fully test the sub I sent and it worked
properly for row THREE and then also for row SEVEN. Perhaps you could change
the 7 to whatever row is desired. I suggest you look at my record of
"ACTUALLY HELPING". BTW, the pay we get for helping here goes to pay for my
Infiniti M45. So, I'm really glad to help a lot.

"Happy Days"

 

[Gord Dibben]

LOL!


Gord