Macros

J

jdorion

I have made up a workbook that I have called master. There is a date
area where the date is manually entered. I was wondering if there was
anyway for someone to put in the date and it automatically adds a new
workbook titled the date entered.

Thanks Jessica
 
N

Nick Hodge

Jessica

You could place some code in the worksheet code module of the sheet in which
the date is entered and then use the worksheet_change event. (No error
checking in code and UK locale settings used)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wbNew As Workbook
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Set wbNew = Workbooks.Add
wbNew.SaveAs Filename:="C:\" & Format(Target.Value, "dd-mm-yyyy")
End If
End Sub

This will check to see if data is being entered in A1. If it is, it will
add a workbook and save it in the root of C:\ with a file name reflecting
the date in A1. (It will error on an unrecognisable date as the date is
reformatted dd-mm-yyyy, as you cannot use forward slashes in a file name)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top