Auto naming of Worksheet tabs in Excel

  Leepaci 09:33 25 Oct 2007

Hello, Are there any Excel Gurus out there can help me with this one please?

I am wanting to setup either a formula or macro that will perform the following.

Eg. When I type a value into cell A1, Excel will automatically rename the Worksheet name to that of the value in cell A1.

I have roughly 150 worksheets that I need to create and thought there must be an easy way to type these values into the cells and for them to automatically create a worksheet with the same name rather than me have to manually create and then name a worksheet for each entry?

Thanks in advance!!

  silverous 09:50 25 Oct 2007

How 'bout this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
ActiveSheet.Name = Target.Text
End If

End Sub

  Leepaci 10:07 25 Oct 2007

Thanks for that silverous, where exactly shoudl I enter that code?

  silverous 10:09 25 Oct 2007

In Excel, right click on the icon immediately to the left of the File menu and choose View Code. (or press alt and f11 together)

Then double click the sheet you want the code on in the pane that appears (e.g. Sheet1) - under Microsoft Excel Objects.

Paste the code into the right hand pane.


  silverous 10:30 25 Oct 2007

One thing to watch out for is if you have anything in that cell that is an invalid worksheet name e.g. with slashes or something in. Could enhance this to check for that depending on requirements.

  Leepaci 10:30 25 Oct 2007

Cool, thanks, I've got that working. Now if I want to click and drag downwards from cell A1 so that I get for example Page1, Page2, Page3, Page4, etc in column A, how could I then change the VB code so that a worksheet is automatically created for each of those cells with the same name? Eg. A1 = Page1, A2 = Page2 A3 = Page3 and so on. Then worksheets would automatically be created with the same name.
Is this possible do you know?

  silverous 11:21 25 Oct 2007

Something like this? It doesn't make use of any existing "Sheet1" etc. so may want some code to delete those if they exist.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
For Each oCell In Target.CurrentRegion

' Check it has a value
If oCell.Text <> "" Then

' Check if there is already a sheet named after this cell
On Error Resume Next
sSheet = ""
sSheet = Worksheets(oCell.Text).Name
On Error GoTo 0

If sSheet = "" Then

' Couldn't find so add
ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = oCell.Text

End If

End If

Next oCell

End If

End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?