Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
create a new worksheet when a cell has a date entered
I am trying to create a new worksheet when a cell has a date entered
into it For instant in Worksheet 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 worksheet would be created and named the date entered into B12 and so on Is this out of the question |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
create a new worksheet when a cell has a date entered
See response in your other post.
"Zane" wrote: I am trying to create a new worksheet when a cell has a date entered into it For instant in Worksheet 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 worksheet would be created and named the date entered into B12 and so on Is this out of the question |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
create a new worksheet when a cell has a date entered
Sure, can do with some considerations - can't have 2 sheets with same name
(date), and sheet names have some limitations, but this should give you a start. It looks specifically in B11 and B12 for a date and if a date was entered in either of those, tries to add the sheet to the end of the workbook. To place this code into where it needs to go, right-click on the sheet tab that you will be entering the dates onto and choose [View Code] from the list that appears. Copy this code and paste it into the code module that appears. Close the VB Editor and give it a spin: Private Sub Worksheet_Change(ByVal Target As Range) Dim iSect As Range Dim mySheet As String Set iSect = Application.Intersect(Target, Range("B11:B12")) If iSect Is Nothing Then Exit Sub End If If Not (IsDate(Target)) Then Exit Sub End If mySheet = ActiveSheet.Name Application.ScreenUpdating = False 'test to see if a sheet with anticipated name 'already exists On Error Resume Next Worksheets(Format(Target.Value, "dd-mmm-yyyy")).Select If Err = 0 Then MsgBox "You already have a sheet for this date. No sheet added." GoTo ExitSheetAdd End If Err.Clear On Error GoTo SheetAddError Application.EnableEvents = False Worksheets.Add after:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = _ Format(Target.Value, "dd-mmm-yyyy") ExitSheetAdd: On Error GoTo 0 Worksheets(mySheet).Select Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub SheetAddError: MsgBox "Error: " & Err.Number & vbCrLf & Err.Description GoTo ExitSheetAdd End Sub "Zane" wrote: I am trying to create a new worksheet when a cell has a date entered into it For instant in Worksheet 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 worksheet would be created and named the date entered into B12 and so on Is this out of the question |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can excel automatically enter the date data in a cell was entered | Excel Worksheet Functions | |||
Date formulas | Excel Discussion (Misc queries) | |||
How do I get ONLY new info from 1 Worksheet to another automatical | Excel Worksheet Functions | |||
How to return a value between date ranges | Excel Worksheet Functions | |||
How do I copy a date in a worksheet cell to another worksheet? | Excel Worksheet Functions |