Tab name matches cell

[Nigel]

Hi

I asked the question a few days ago how to get a tab name to match the data in cell h2. Somebody kindly gave the following formula..

Private Sub Worksheet_Activate()
Sheets(1).Name = Range("H2").Value
End Sub

I must be doing something wrong as it doesnt seem to work. I have entered the formula by right clicking on the tab of the open worksheet ...clicking on "view code" and pasted it into PDFWriter.xla - module1 (Code).....as you may have already gathered I havent got a clue what Im doing so any help in the simplest of terms would be greatly appreciated

Regards

Nigel

[Jim Thomlinson]

The code you have only works when it is embeded right in the code for the
specific sheet. You can not put it in PDFWriter.xla. You are correct in that
you select the appropraite tab and right click. Selecting View Code will take
you to the code for that sheet. In the project explorer (on the left) you
will see that the sheet that you right clicked on is the one highlighted.
This is the only place where that code will work.
--
HTH...

Jim Thomlinson


"Nigel" wrote:

>
> Hi
>
> I asked the question a few days ago how to get a tab name to match the
> data in cell h2. Somebody kindly gave the following formula..
>
> Private Sub Worksheet_Activate()
> Sheets(1).Name = Range("H2").Value
> End Sub
>
> I must be doing something wrong as it doesnt seem to work. I have
> entered the formula by right clicking on the tab of the open worksheet
> ...clicking on "view code" and pasted it into PDFWriter.xla - module1
> (Code).....as you may have already gathered I havent got a clue what Im
> doing so any help in the simplest of terms would be greatly appreciated
>
> Regards
>
> Nigel
>
>
> --
> Nigel
> ------------------------------------------------------------------------
> Nigel's Profile: http://www.excelforum.com/member.php...o&userid=25696
> View this thread: http://www.excelforum.com/showthread...hreadid=394755
>
>

[Nigel]

Many thanks for your help........and I now realise where I went wrong. Could I trouble you for an extra line of code which will put a default tab name of "est 1" in if "h2" is empty please.

Thanks for your time and regards

Nigel