Hi,
I was searching the Internet to automate the renaming of sheets by referencing a cell in each sheet, and I came across the following code which have been made into a macro and stored in a workbook called Master:-
The formatting of cell B4 is dd-mmm-yy, Is there a way that I can format the sheet name to be in dd-mm-yy format?
I have a sheet called Totals and I would like cell A2 to become the 1st of the Month of the year entered in B4 sheet1 (i.e. if B4 is 03-Jan-2021, then A2 to become 01-01-23)
Additionally, I would like to save the workbook as 01 January, 02 February, 03 March etc. (i.e. month number followed by the full month description) depending on the value in cell B4 in sheet1.
Example if B4 in sheet1 has a date of Jan 2023, then the workbook should be named 01 January, if B4 in sheet1 is Feb 2023, then the workbook should be named 02 February etc, etc etc.
The workbooks should be stored in the same folder that workbook Master is in.
Rather than running a macro can something be done to automatically achieve the above once a date has been put into cell B4 on Sheet1?
Any assistance offered would be gratefully received.
I was searching the Internet to automate the renaming of sheets by referencing a cell in each sheet, and I came across the following code which have been made into a macro and stored in a workbook called Master:-
VBA Code:
Sub RenameSheets()
Dim ws As Worksheet
On Error GoTo err_chk
For Each ws In Worksheets
If Len(Trim(ws.Range("B4"))) > 0 Then ws.Name = ws.Range("B4").Text
Next ws
On Error GoTo 0
Exit Sub
err_chk:
MsgBox "Error #:" & Err.Number & ": " & Err.Description, vbOKOnly, "ERROR RENAMING " & ws.Name
Err.Clear
Resume Next
End Sub
The formatting of cell B4 is dd-mmm-yy, Is there a way that I can format the sheet name to be in dd-mm-yy format?
I have a sheet called Totals and I would like cell A2 to become the 1st of the Month of the year entered in B4 sheet1 (i.e. if B4 is 03-Jan-2021, then A2 to become 01-01-23)
Additionally, I would like to save the workbook as 01 January, 02 February, 03 March etc. (i.e. month number followed by the full month description) depending on the value in cell B4 in sheet1.
Example if B4 in sheet1 has a date of Jan 2023, then the workbook should be named 01 January, if B4 in sheet1 is Feb 2023, then the workbook should be named 02 February etc, etc etc.
The workbooks should be stored in the same folder that workbook Master is in.
Rather than running a macro can something be done to automatically achieve the above once a date has been put into cell B4 on Sheet1?
Any assistance offered would be gratefully received.
Last edited by a moderator: