# RENAME SHEET WITH NEW DATE BASED ON EXISTING SHEETS



## ravecake (Dec 23, 2022)

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 (Dec 23, 2022)

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 (Dec 23, 2022)

dmt32 said:


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


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 (Dec 23, 2022)

ravecake said:


> 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 (Dec 23, 2022)

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!


----------

