Can Excel Be Use to Show Date Automatically

Hello all,
Please bear with me for few mintues.

I have made a small program for keeping track of a Production Store. The Sheet looks like this:-

Date=_________ Day:___________

PRODUCTION STORE


PRODUCT OP.BAL PRODUCE DEliver CL.BAL.

Door 3 2 1 =3+2-1=4
Window 4 1 4 =4+1-4=1


Op.Bal:-
is the column which represents the quantity of product which you have at the start of the day.

Cl. Bal:-
Is something which shows what is left at the end of the day in the store. after add what is produced today and subtracting what is delievered.

At this time when we open the sheet we have to make input for the day , date , Op Bal. , Produce , Deliever.


My objective is:-

1) Now i want to know how i can make excel to show date and day it self.( Offcourse when we will enter the date and day for the first time when the sheet is used to give a reference point )


2) On the start of each day a new sheet appears showing the date and day and the Closing balance of the previous day appears in the Op.Bal of this day. e.g.

Lets suppose today is the first day when we start using the excel sheet. we will make all the entries.


Date:- 1 March Day:- Thuesday



PRODUCTION STORE


PRODUCT OP.BAL PRODUCE DEliver CL.BAL.

Door 3 2 1 =3+2-1=4
Window 4 1 4 =4+1-4=1



Now on 2 March , wednesday, when we open excel for makle the entries of new day..The Excel Sheet automatically shows the new day and date and the Closing balance in the opening balance column. the sheet should look like this:-

Date:- 2 March Day:- wednesday


PRODUCTION STORE


PRODUCT OP.BAL PRODUCE DEliver CL.BAL.

Door 4 Window 1


so if this works than we only have to make 2 inputs for produce and deliver.

I have a VB Code for this but it uses a Command of Cltr+Shift+S and this command opens a new sheet in the same file which has the name of the date of that day and it copies the enter data from previous sheet to the new one but with chnaging columns from Cl. to op Bal.
But this is not working well enough ...i need to use automatic function of excel..


Option Explicit

Public Sub InsertTodaysSheet()

Dim newSheet As Worksheet
Dim yestSheet As Worksheet
Dim temp As Date
Dim tempDate As Date
On Error Resume Next
yestSheet = ActiveWorkbook.Worksheets(Format$(Date, "yyyy-mm-dd"))
If (Err.Number = 0) Then
Exit Sub
End If

tempDate = 0
On Error Resume Next
For Each yestSheet In ActiveWorkbook.Worksheets
temp = CDate(yestSheet.Name)
If (Err.Number = 0) Then
If (tempDate < temp) Then
tempDate = temp
End If
End If
Err.Clear
Next yestSheet
On Error GoTo 0
Set yestSheet = ActiveWorkbook.Worksheets("Draft")
Set newSheet = ActiveWorkbook.Worksheets.Add(yestSheet)
yestSheet.Cells.Copy newSheet.Cells
newSheet.Name = Format$(Date, "yyyy-mm-dd")
If (tempDate > 0) Then
Set yestSheet = ActiveWorkbook.Worksheets(Format$(tempDate, "yyyy-mm-dd"))
yestSheet.Range("E9:E28").Copy
newSheet.Range("B9:B28").PasteSpecial xlPasteValues
yestSheet.Range("K9:K28").Copy
newSheet.Range("H9:H28").PasteSpecial xlPasteValues
End If
newSheet.Range("C1").Value = Format(Date, "Short Date")
newSheet.Range("E1").Value = Format$(Date, "dddd")
Set newSheet = Nothing
Set yestSheet = Nothing
End Sub

[Escalus]

Suggest that :-
Name your sheet "Today" (or whatever).
Format cell E1 as dddd, and enter =C1
Try the following :-

Private Sub Workbook_Open()
With Sheets("Today")
If Application.CountA(.[C8:D28]) = 0 Then
.[C1] = Date
Exit Sub
ElseIf .[C1] <> Date Then
.Copy After:=Sheets("Today")
.Next.Name = Format([C1], "yyyy-mm-dd")
.[C1] = Date
.[B8:B28] = [E8:E28].Value
.[C8:D28].ClearContents
.Activate
End If
End With
End Sub

Note : You might want to add an error handler to deal with the situation where the new sheet name being created already exists.