RENAME SHEET WITH NEW DATE BASED ON EXISTING SHEETS

[ravecake]

Hi,

I have an existing workbook of sheets ordered in decreasing dates (by business day) as such: [ 20221221 ] [ 20221220 ] [ 20221216 ] [ 20221215 ].

With each new day I will paste a new sheet of data into that workbook as such: [ 20221222 ] [ 20221221 ] [ 20221220] [ 20221216 ] [ 20221215 ]

How can I write a script so that once I have pasted that sheet into the workbook (which is extracted from my external database as Sheet1) it is renamed to the date one business day after the previous sheet (i.e. the sheet to the right -> [ 20221221 ] in the above example)?

MANY THANKS!!

 

[dmt32]

Hi,
place following code in the Thisworkbook Code Page & see if it will do what you want

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim SheetName   As Variant
    Dim dDate       As Date
    SheetName = Val(Sheets(Sh.Index + 1).Name)
    If Len(SheetName) <> 8 Then Exit Sub
    dDate = DateSerial(Left(SheetName, 4), Mid(SheetName, 5, 2), (Right(SheetName, 2))) + 1
    Sh.Name = Format(dDate, "YYYYMMDD")
End Sub

Hope Helpful

Dave

 

[ravecake]

Hi,
place following code in the Thisworkbook Code Page & see if it will do what you want

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim SheetName   As Variant
    Dim dDate       As Date
    SheetName = Val(Sheets(Sh.Index + 1).Name)
    If Len(SheetName) <> 8 Then Exit Sub
    dDate = DateSerial(Left(SheetName, 4), Mid(SheetName, 5, 2), (Right(SheetName, 2))) + 1
    Sh.Name = Format(dDate, "YYYYMMDD")
End Sub

Hope Helpful

Dave

Thanks for your quick response Dave.

I want to store this macro in my personal.XLSB, if I paste your code into a module and execute it just opens the macro dialog box and doesn't run the code.

 

[dmt32]

Thanks for your quick response Dave.

I want to store this macro in my personal.XLSB, if I paste your code into a module and execute it just opens the macro dialog box and doesn't run the code.

Need to place code where stated in the Thisworkbook code page - when you insert new sheet this event triggers the code.

Dave

 

[ravecake]

Hi Dave,

I need to keep the workbooks in question as .xlsx, and so have kept any macros to use in personal.xlsb.

I managed to find a separate solution via FormulaR1C1 functions as follows:

Sub sheetvalue( )

    Range("AA2").Value = ActiveWorkbook.Worksheets(2).Name
    Range("AA3").Select
    ActiveCell.FormulaR1C1 = _
        "=DATE(LEFT(R[-1]C,4),MID(R[-1]C,5,2),RIGHT(R[-1]C,2))"
    Range("AA4").Select
    ActiveCell.FormulaR1C1 = "=WORKDAY(R[-1]C,1)"
    Range("AA4").Select
    Selection.NumberFormat = "YYYYMMDD"

With ActiveSheet
    .Name = Format(Range("AA4").Value, ("YYYYMMDD"))

End With

End Sub

This assumes that ActiveWorkbook.Worksheets(2) points to the most recently inserted previous-day's sheet.

Cells AA2/3/4 are just empty cells used for the purpose of extracting existing sheet names and applying the WORKDAY function.

Is there a way to clean this up or avoid using FormulaR1C1 in the interest of curiosity?

Thanks!