Inserting VBA code to auto rename sheet name to current date

keim 0
Viewed 29

I am trying to generate a report in the form of another sheet in excel. I would like to establish a macro that runs creates this report and also automatically renames the generated sheet to current date.

This is the code I have now by using Record Macro.

    Sub Macro_NWP()
        '
        ' Macro_NWP Macro
        '
        ' Keyboard Shortcut: Ctrl+n
        '
        Sheets.Add After:=ActiveSheet
        Sheets("Sheet1").Select
        Sheets("Sheet1").Name = "09-07-22"
        Sheets("Project Status").Select
        ActiveWorkbook.Worksheets("Project Status").AutoFilter.Sort.SortFields.Clear
        ActiveSheet.ShowAllData
        ActiveSheet.Range("$A$1:$Y$290").AutoFilter Field:=3, Criteria1:= _
    "In Progress"
        Columns("A:C").Select
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        Range("A:C,F:F").Select
        Range("F1").Activate
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 12
        Range("A:C,F:F,M:Y").Select
        Range("M1").Activate
        Selection.Copy
        Sheets("09-07-22").Select
        ActiveSheet.Paste
        Selection.ColumnWidth = 20
        Columns("B:B").Select
        Selection.ColumnWidth = 50
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 10
        Range("M:M,P:P").Select
        Range("P1").Activate
        Selection.ColumnWidth = 60
        ActiveWindow.Zoom = 90
        ActiveWindow.Zoom = 80
        ActiveWindow.Zoom = 70
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 1
        Range("E2:J2").Select
        ActiveWindow.FreezePanes = True
        ActiveWindow.SmallScroll Down:=-6
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        Range("E2").Select
    End Sub

Hoping to get some insights on how I can insert a code that auto renames the created sheet to current date (mm-dd-yy).

vba excel
1 Answers

I write some code as a demo:

Dim ws As Worksheet
Sheets.Add After:=ActiveSheet
Set ws = ActiveSheet
ws.Name = Format(Date, "dd-mm-yy")
Abel Wong0
Related