# VBA - Rename all sheet names by adding year



## ravecake (Jan 3, 2023)

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 (Jan 3, 2023)

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 (Jan 3, 2023)

kevin9999 said:


> Try this:
> 
> ```
> Sub Add_Year()
> ...



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 (Jan 3, 2023)

ravecake said:


> 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 (Jan 3, 2023)

kevin9999 said:


> 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 (Jan 3, 2023)

ravecake said:


> 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 (Jan 3, 2023)

kevin9999 said:


> To add hyphens where they don't currently exist, use:
> 
> ```
> For Each ws In ActiveWorkbook.Worksheets
> ...


Ahh perfect, thanks so much! Appreciate your help.


----------



## kevin9999 (Jan 3, 2023)

ravecake said:


> Ahh perfect, thanks so much! Appreciate your help.


You're welcome, and thanks for the feedback


----------

