Worksheet Tab Name from Cell C4

[JAK]

I have a worksheet that I would like to have the Tab automatically rename
itself to whatever value is in Cell C4. This worksheet will be copied (via
copy worksheet macro) multiple times (move to end with create a copy option
checked). I have never used VBA in Excel so I am not sure how to make this
work. I tried using what you had suggested for Wally but couldn't make that
work for me.

 

[Gary Brown]

In the VBEditor, put the following in the worksheet to be copied.
Everytime you go to that worksheet, the macro will fire and change the
worksheet's name to whatever is in cell C4 of that worksheet. If the name is
illegal, the worksheet will not change the name.

Private Sub Worksheet_Activate()
On Error Resume Next
ActiveSheet.Name = Range("C4").Value
End Sub

--
Hope this helps.
If this post was helpfull, please remember to click on the ''YES'' button at
the bottom of the screen.
Thanks,
Gary Brown

 

[Tom Hutchins]

If you are new to macros, this link to Jon Peltier's site may help you
implement Gary's code:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[JAK]

Gary,

Thank you so much, your very simple code and clear instructions worked very
well! I do have one follow up question however. I currently have a macro to
copy the original worksheet named 'server'. If I change the name of the
worksheet tab with this code it breaks the macro to copy. Can you tell me
how to make "Server" a variable for the current worksheet. Also, is there a
way to tell it to copy to the end instead of after "Sheet (2)"

Sub CopySheet()
'
' CopySheet Macro
' Macro recorded 6/23/2008 by Jason A. Koller
'

'
Sheets("Server").Select
Sheets("Server").Copy After:=Sheets(2)
End Sub

Thanks again Gary... and Tom for the link to learn about how to use someone
elses macro's!

Jason

 

[Tom Hutchins]

Here is one way...

Sub CopySheet()
'Copies the active sheet after all other sheets.
ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
End Sub

The code above will create a copy of the active worksheet. The new worksheet
will be after all the other worksheets. Copy this macro into a VBA module in
your workbook. Make sure the 'Server' sheet is the active sheet when you run
it (make sure it is the sheet that is showing, and click a cell on it to make
sure).

I wrote it to use the active sheet because it sounds like this sheet starts
out as 'Server' but its name may get changed via Gary's event code.

Hope this helps,

Hutch

 

[JAK]

Hutch,

Your assumption of what my request is spot on, that worked exactly as I
hoped it would.

I appreciate you and Gary sharing your knowledge and expertise.

Best regards,

Jason