VBA - Rename all sheet names by adding year

[ravecake]

Hi,

I have a workbook of sheets in descending working-day order in "M-D" format as follows: [ 12-30 ] [ 12-29 ] [ 12-28 ] [ 12-23 ] [ 12-22 ] ... [ 7-1 ] etc. This goes back multiple months.

Given the new year, I would like to write a macro that will rename all sheets in "YYYY-M-D" format. In other words, "2022-" will be added to the beginning of each sheet name as follows: [ 2022-12-30 ] [ 2022-12-29 ] [ 2022-12-28 ] ... [ 2022-7-1 ] etc.

Separately, I would also like to know if all sheets can be re-formatted in "YYYY-MM-DD", e.g. [ 2022-7-1 ] → [ 2022-07-01 ].

Would appreciate any assistance!
Thanks.

 

[kevin9999]

Try this:

Sub Add_Year()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Name = "2022-" & ws.Name
        ws.Name = Format(ws.Name, "YYYY-MM-DD")
    Next ws
End Sub

 

[ravecake]

Try this:

Sub Add_Year()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Name = "2022-" & ws.Name
        ws.Name = Format(ws.Name, "YYYY-MM-DD")
    Next ws
End Sub

Thanks for the quick response - I needed this as a personal.xlsb macro but the above wouldn't work (nothing would happen when executing). It does however work when I change ThisWorkbook to ActiveWorkbook in the code. Any idea why this is?

 

[kevin9999]

Thanks for the quick response - I needed this as a personal.xlsb macro but the above wouldn't work (nothing would happen when executing). It does however work when I change ThisWorkbook to ActiveWorkbook in the code. Any idea why this is?

ThisWorkbook is the workbook that contains the code (which is what I assumed you wanted) whereas ActiveWorkbook is whatever workbook you happen to have active when you run the code.

 

[ravecake]

ThisWorkbook is the workbook that contains the code (which is what I assumed you wanted) whereas ActiveWorkbook is whatever workbook you happen to have active when you run the code.

Yeah this makes sense.

Just while I have you here - if all sheets are named in YYYYMMDD [ 20221230 ] [ 20221229 ] etc how can I rename them all to YYYY-MM-DD?

Conversely, if they are all in YYYY-MM-DD how do I remove the hyphens and reformat to YYYYMMDD?

Thanks!

 

[kevin9999]

Just while I have you here - if all sheets are named in YYYYMMDD [ 20221230 ] [ 20221229 ] etc how can I rename them all to YYYY-MM-DD?

Conversely, if they are all in YYYY-MM-DD how do I remove the hyphens and reformat to YYYYMMDD?

To add hyphens where they don't currently exist, use:

For Each ws In ActiveWorkbook.Worksheets
        ws.Name = Format(ws.Name, "YYYY-MM-DD")
Next ws

To remove the hyphens where they currently exist, use:

For Each ws In ActiveWorkbook.Worksheets
        ws.Name = Format(ws.Name, "YYYYMMDD")
Next ws

 

[ravecake]

To add hyphens where they don't currently exist, use:

For Each ws In ActiveWorkbook.Worksheets
        ws.Name = Format(ws.Name, "YYYY-MM-DD")
Next ws

To remove the hyphens where they currently exist, use:

For Each ws In ActiveWorkbook.Worksheets
        ws.Name = Format(ws.Name, "YYYYMMDD")
Next ws

Ahh perfect, thanks so much! Appreciate your help.

 

[kevin9999]

Ahh perfect, thanks so much! Appreciate your help.

You're welcome, and thanks for the feedback