Sheet Renameing which has a date

[Kayslover]

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:-

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.

 

[Kayslover]

OK, now I understand

I have the following after the DIM statement:-

path="D:\Till Takings\2023\"

I then have ActiveWorkbook.SaveAs Filename:=Path & "01 January.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled.

This works a treat and saves the files as defined by Path.

However, rather than hard coding the path, is there any way of extracting it from the location of where the template is stored (i.e."D:\Till Takings\2023\", in 2023, it will become "D:\Till Takings\2024\" in 2024) etc.etc etc.

The following formula gives me the exact location of the place that the template is in:-

=SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-12),"[","")

I need to somehow have this in the macro.

 

[drsarao]

If the macro is within the file (template) which is in that directory then:
path=ThisWorkbook.Path & "\" should work nicely.

 

[drsarao]

You can also:
path=range("A1") 'wherever you put the formula for the path
But this workbook.path method is the more direct way.

 

[Kayslover]

I have replaced the hard coded path with Path = ThisWorkbook.Path & "\"

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

However, when I run the macro to copy the sheets, it seems to be doing nothinh. What I mean by that it was previously, creating files called 01 January, 02 February etc to the hard code path, now no files are created.

Any ideas?

 

[drsarao]

debug.print the 'path' and check

 

[Kayslover]

Sorry, I don't understand what you mean.

Please expand.

 

[Kayslover]

Double clicked on the template and it created a workbook called Master1.xlsm. Ran the macro to rename the sheets and ran the second macro to save files to 01 January, 02 February etc.

This macro failed, and I had to manually save Master1.xlsm to the appropriate folder.

Ran the second macro to save files to 01 January, 02 February etc. and it created 01 January, 02 February as expected.

I changed the first macro which renames the sheet names to save Master1.xlsm to the appropriate folder (along with the code Path = ThisWorkbook.Path & "\" and ActiveWorkbook.SaveAs Filename:=Path & "Master1.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

But it does not save the workbook to the path.

I have to manually save where required and then run the second macro.

Not the perfect solution, but 99% there, so it will have to do.

Thanks for your assistance

 

[drsarao]

You are welcome!