Function to name a worksheet after a cell?

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Thanks in advance for the help.

I am trying to autoname worksheets in a workbook after a
specific cell on the worksheet. In this case, I want the
worksheet name to automatically be name the same thing as
the value in cell K3.

Can anyone guide me in the right direction?

Thanks,
Brian
 
Brian,

Do you want it to automatically update when you type in the cell? Assuming
so, add this code to the appropriate worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit:
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Thanks for the speedy reply, but I'm afraid that is a bit
over my head.
Yes, I would like it to update automatically when info is
typed in the cell, but I don't know what a 'worksheet code
module' is.
Can you give me a little more help on this one?

Thanks again,
Brian
 
Brian,

On the worksheet that you want it to happen

On the sheet tab name, right-click, and select View Code. This will take you
into the sheet code module, so just paste the code I supplied into the code
pane which will open up and the cursor will already be located there.

Just change the reference to Range("A1") to the appropriate cell for you.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Thanks a million for you help.
But I guess I should have mentioned that it is a date that
I am entering in the cell, and when I do this it doesn't
work. If I enter anything else (just text or numbers) it
works find.

Then on subsequent worksheets, I have a formula to add 7
to the date on the first sheet to auto date these sheets.

Thanks again,

Brian
 
Brian,

Easy enough

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit:
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsDate(Target) Then
ActiveSheet.Name = Format(Target.Value, "dd mmm yyyy")
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Thanks again, that worked.
One more piece of the puzzle, on subsequent worksheets I
have a formula to place the date in based on the value of
K3 on the previous worksheet (='PREVIOUS WORKSHEET NAME'!
K3+7).

On this subsequent sheets, I am trying to get them to
autoname based on the entry of the first date on the first
worksheet, is this possible?

Thanks so much.
 
Brian,

Not sure that I fully understand what you are asking, but let's take a stab
at it.

This code will work when the date is entered in A1 on the first sheet. The
tab name on that sheet is change to that date, and each subsequent tab name
is incremented by 7 days. Try it and see how it compares

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet

Application.EnableEvents = False
On Error GoTo ws_exit:
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsDate(Target) Then
For Each ws In ThisWorkbook.Worksheets
ws.Name = Format(Target.Value + ((ws.Index - 1) * 7), "dd
mmm yyyy")
Next ws
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Yes sir!!!
That worked exactly as I wanted.
Thanks so very much for all you help and patience

Brian
 
Hello,
I work for a school, and we use Excel to do our report
cards. I am trying to get it so the teachers can use
a "master sheet" to enter in all the student names with
grades, and then have one other sheet per student, using
the info from the master sheet (So if there were 20
students in the class, there would be 21 worksheets: 1
master and 20 report cards). The individual report card
sheets contain a "template" that looks like the student's
report card, and currently all the data gets manually put
in to each worksheet.

The first thing that I want to do is name the other sheets
to correspond with the students name, and the formula you
created below looks to be what I am looking for.
Unfortunetly, when I try to change the value of Range
("A1") it doesn't seem to work. I am using Range("Sheet3!
A1"), where "Sheet3" is the master sheet, and "A1" is
Student 1 on Sheet 3. Is there something different that I
should do when trying to reference a different sheet?

Also, and we can get to this question later if that's
easier, I would like to be able to copy a sheet that has
references to the master sheet, and have it apply to the
next sheet that is created, but for the next student.
Example is if I have the "Reading" section of the student
sheet taking the value from the master sheet using the
formula ='Sheet3!'B4, is there a way that I can make the
copy automatically use the value 'Sheet3!'B5, which would
be the next "Reading" grade for the next student on the
list?

Sorry if I was a little long winded, and I appreciate any
help you can give to me.

Thanks,

Joe
 
Joe,

Just noticed your post. Have you resolved it?

The first problem is that you just put the same code in the worksheet 3 code
module, not worksheet 1. Also, if the names are in A1:A20, say, the test
would be

If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
ActiveSheet.Name = Target.Value
End If

On the second point, rather than using a direct reference, you could use a
lookup. So for instance, if the name on Sheet1 is in cell A1 say, to get the
column B value from Sheet3 at any point, you would use

=VLOOKUP(A1,Sheet3!A1:A20,2,FALSE)

and this will automatically update when you change the name.

What I would probably do is to have a student template sheet, and hide it
let's cal it Template. I would then use the Master sheet to add the names
and automatically create a new worksheet based upon the Template, with code
similar to this on the Master sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oMaster As Worksheet
Dim oWS As Worksheet

Application.EnableEvents = False
On Error GoTo ws_exit
Set oMaster = ActiveSheet
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
On Error Resume Next
Set oWS = Worksheets(Target.Value)
If oWS Is Nothing Then
On Error GoTo 0 'ws_exit
Worksheets("Sheet2").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Target.Value
oMaster.Activate
End If
On Error GoTo ws_exit
End If

ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top