Sheet Renameing which has a date

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
168
Office Version
  1. 2013
Platform
  1. Windows
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:-

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:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
If B4 is a real date then Replace:
If Len(Trim(ws.Range("B4"))) > 0 Then ws.Name = ws.Range("B4").Text

with:
If Len(Trim(ws.Range("B4"))) > 0 Then ws.Name = Format(ws.Range("B4"), "dd-mm-yy")
 
Upvote 0
Drsarao,

Thanks for that, easy when you know.

Any assistance on the rest would be apprecaited.
 
Upvote 0
Any assistance on the rest would be apprecaited.
This needs additional info.

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.

Presuming the Code and Data (worksheets) are in "Master" workbook.
The newly created and named Workbooks will be a Copy of Master (containg all the sheets and/or the macro)? We will need to use workbook SaveAs method using new name. Saved as xlsx or xlsm depending on inclusion or otherwise of the macro.
Or it will contain just the Worksheet being processed at the time. We create a new workbook -> Copy the current Sheet to it -> save that workbook using new name.

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?
This is possible. Look at Worksheet_SelectionChange event in VBA help.
 
Upvote 0
Thanks for your kind assistance and pointing to what commands need to be used (I have never done VBA and therefore do not know any of the commands).

Will give it a go.
 
Upvote 0
I have an Excel Template to do the above.

How do I save the new files in the same folder as the template?
 
Upvote 0
I donot understand how you are using template to do the tasks.
In a macro:
First find and save the directory path by ThisWorkbook.Path
In the SaveAs method prefix the path to filename and save.
 
Upvote 0
Hi,

I am using a home created template to do the following:-

The template is in D:\Till Takings\2023 (this will change to another structure when I caopy the template at my local Social Club).

Ask for a date to be entered in B4 on Sheet1, B5 adds 1 to the value in B4 (upto B10). B4 in sheet1 adds 1 to B10Sheet1 and repeat for other sheets.

Have a macro called RenameSheets which renames sheets to the valuse of B4 on sheets.

Have a macro called CopySheets which thake the existing workbook and save as 01 Jan, 02 Feb etc.

The files currently get saved to users\user\document folder.

I want to save (in my example) D:\Till Takings\2023\

I have changed the following command:-

ActiveWorkbook.SaveAs "01 January.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled to
ActiveWorkbook.SaveAs Filename:=Path & "01 January.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

But it still saves to the user document folder.

Any further help would be apprerciated.
 
Upvote 0
Did you?:
path=" D:\Till Takings\2023\ "
 
Upvote 0
Did you?:
path="D:\Till Takings\2023\"
(inadvertant spaces in previous post)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top