Prev: Sumif comparing dates in criteria
Next: highlight the row of the cell Im working in?
From: Christine on 2 Apr 2010 13:27
Hello all,

I have found a lot of posts about how to populate a cell with the value in
the worksheet tab name, but not the other way around. I would like to
automatically name the worksheet tab based on the value in a specific cell.

I'm ok with formula's but not "up" on VB. Is there a formula I could use to
accomplish this?

Thanks in advance for any help you can give me.

Christine
From: Paul on 2 Apr 2010 13:32

Hi Christine,

A formula cannot name a worksheet, VBA would be required. For
example:



Code:
--------------------



Sub nameSheet()
ActiveSheet.Name = ActiveSheet.Range("A1").Value
End Sub

--------------------



This would name the activesheet the same as the value in cell A1. Just
remember that sheet names can't contain various characters. If A1 is
just text or numbers, no issues.


--
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192563

http://www.thecodecage.com/forumz

From: Roger Govier on 2 Apr 2010 13:48
Hi Christine

These 2 subs will do the task.
The first will just name the Active Sheet.
The second will run through the file and name all sheets according to
the value in cell A1 - provided A1 contains a value.

Sub NameSheet()
If Range("A1") <> "" Then
ActiveSheet.Name = Range("A1").Value
End If

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

End Sub

Sub NameSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Range("A1") <> "" Then
ws.Name = ws.Range("A1").Value
End If
Next ws
End Sub

To install
Copy code as above
Alt+F11 to invoke the Vb Editor
Alt+I+M to Insert a new module
Paste the code into the white pane that appears
Alt+F11 to return to Excel

To Use
Alt+F8
Select the macro to use
Run

--
Regards
Roger Govier

Christine wrote:
> Hello all,
>
> I have found a lot of posts about how to populate a cell with the value in
> the worksheet tab name, but not the other way around. I would like to
> automatically name the worksheet tab based on the value in a specific cell.
>
> I'm ok with formula's but not "up" on VB. Is there a formula I could use to
> accomplish this?
>
> Thanks in advance for any help you can give me.
>
> Christine
 | 
Pages: 1
Prev: Sumif comparing dates in criteria
Next: highlight the row of the cell Im working in?