Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Can Excel Be Use to Show Date Automatically

  1. #1
    Guest

    Default

    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


  2. #2

    Join Date
    Feb 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •