Sheet Renameing which has a date

Kayslover

New Member
Joined
Sep 22, 2020
Messages
41
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:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,141
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")
 

Kayslover

New Member
Joined
Sep 22, 2020
Messages
41
Office Version
  1. 2013
Platform
  1. Windows
Drsarao,

Thanks for that, easy when you know.

Any assistance on the rest would be apprecaited.
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,141
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.
 

Kayslover

New Member
Joined
Sep 22, 2020
Messages
41
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

Kayslover

New Member
Joined
Sep 22, 2020
Messages
41
Office Version
  1. 2013
Platform
  1. Windows
I have an Excel Template to do the above.

How do I save the new files in the same folder as the template?
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,141

ADVERTISEMENT

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.
 

Kayslover

New Member
Joined
Sep 22, 2020
Messages
41
Office Version
  1. 2013
Platform
  1. Windows
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.
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,141
Did you?:
path="D:\Till Takings\2023\"
(inadvertant spaces in previous post)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,959
Members
410,586
Latest member
acadavid86
Top