how can you rename a workgroup from a cell

  • Thread starter Thread starter Zane
  • Start date Start date
Z

Zane

I am trying to create a new work group when a cell has a date entered
into it

For instant in Workbook 1 you type into cell B11 a date
I would like to automaticly create a new worksheet and call it that
date entered to B11
Then if I type a Date into cell B12 a new workbook would be created and
named the date entered into B12
and so on

Is this out of the question
 
Try this. Right click on your worksheet and paste this code into the code
module. Change the range "B11:B12" to whatever range you have in mind. Or,
if you want to use the entire column B, change
If Not Intersect(Target, Range("B11:B12")) Is Nothing Then
to
If Target.Column = 2 Then

Since worksheet names cannot contain special characters, such as "/", I
replaced the "/" with periods "."


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

If Not Intersect(Target, Range("B11:B12")) Is Nothing Then
On Error Resume Next
Set wksTemp = Sheets(Replace(Target.Value, _
"/", ".", 1, -1, vbTextCompare))
On Error GoTo 0
If wksTemp Is Nothing And IsDate(Target.Value) Then
Set wksTemp = Sheets.Add(after:=Sheets(Sheets.Count))
wksTemp.Name = Replace(Target.Value, _
"/", ".", 1, -1, vbTextCompare)
Target.Parent.Activate
End If
End If

End Sub
 
Back
Top