Rename worksheet using date entered in cell

[steve.waye]

Hi all,

I'm after a way to automatically rename a worksheet based on a date entered in cell D2.


The date has slashes (/) so I'm aware these can't be used in the name of the worksheet. I assume they can be changed in the code.


It would be ideal if a date of 28/10/18 in cell D2 became 28-Oct-18 as the sheet name.


Also, I want to be able to click a button on the active worksheet to create a copy (new worksheet) and have a popup that allows you to enter the date for cell D2 on the new worksheet, which will subsequently rename the new worksheet.


Hope this makes sense.


It's been a while since I did any VBA so guidance on where to paste the code would also be appreciated.


Thanks.

 

[Momentman]

I think I have it...

Sub NewWorksheet()
    Dim DateD2, NewWks As Worksheet
    DateD2 = ActiveSheet.Range("D2").Value
    ActiveSheet.Copy after:=ActiveSheet
    Set NewWks = ActiveSheet
    ActiveSheet.Range("C9:E15").Clear
    ActiveSheet.Range("G9:G15").Clear
    ActiveSheet.Unprotect
    NewWks.Range("D2").Value = DateD2 + 7
    NewWks.Range("D2").Cells.Locked = True
    ActiveSheet.Protect
    ActiveSheet.Name = Format(Range("D2"), "dd-mmm-yy")
End Sub

Thanks for all your help Minuteman. It has encouraged me to get back into VBA (it's been a while).

Cheers.

It is Momentan

Range.Clear would clear both contents nd formatting, while Range.ClearContents would clear only the content

 

[steve.waye]

It is Momentan

Range.Clear would clear both contents nd formatting, while Range.ClearContents would clear only the content

Thanks Momentan (or is it Momentman as per your username)?


That last bit of code helped immensely.


Using "Range.Clear" resulted in having to write additional code to reformat all of the associated cells.


"Range.ClearContents" is so much better.


Thanks again.