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]

You could do it this way---simplistic codes

Sub RenameWorksheet()
    ActiveSheet.Name = Format(Range("D2"), "dd-mmm-yy")
End Sub

For the second code, i am not sure if you are creating a copy of the current sheet or just adding a new worksheet

Sub NewWorksheet()
    Dim DateD2, NewWks As Worksheet
    DateD2 = InputBox("Please Enter value for Cell D2 in dd-mmm-yy", "ENTER DATE")
    Set NewWks = Worksheets.Add(after:=Worksheets(ActiveSheet.Name))
    NewWks.Range("D2").Value = DateD2 'sets the value typed by the user into D2
    'ActiveSheet.Name = DateD2 'if you choose to rename the sheet
End Sub

You can build off that

 

[steve.waye]

Sorry but it's been a while since I did any coding.

Where should I place the first code? In a Module? And how is it triggered?

 

[steve.waye]

Yes, I'm wanting a copy of the original sheet but in the process I want the user to change the date for cell D2.

 

[Momentman]

Yes, I'm wanting a copy of the original sheet but in the process I want the user to change the date for cell D2.

The
2nd code should look like this

Sub NewWorksheet()
    Dim DateD2, NewWks As Worksheet
    DateD2 = InputBox("Please Enter value for Cell D2 in dd-mmm-yy", "ENTER DATE")
    ActiveSheet.Copy after:=ActiveSheet
    Set NewWks = ActiveSheet
    NewWks.Range("D2").Value = DateD2 'sets the value typed by the user into D2
    'ActiveSheet.Name = DateD2 'if you choose to rename the sheet
End Sub

Procedure would be
1. Press ALT+F11 to open the Visual Basic Editor
2. Go to the insert tab and select Module or ALT+I+M
3. Paste the code in the module
4. On the Worksheet, you can INSERT a shape, RIGHT CLICK it and select ASSIGN MACRO and select any of the macros you have pasted in the module

That way when you click the SHAPE, the macro is triggered

You could also use Buttons, from the developer Tab

Hope this helps

 

[steve.waye]

Thanks Momentman. I think I've almost got it working.

There is nothing to force the user to use the required date format. If I input dd/mm/yy the process stalls due to trying to rename the sheet with a slash.

I changed line 7 of the code to your original suggestion and it worked OK.

One last question.

Instead of using a popup, can I simply make D2 in the new sheet 7 days after D2 in the original sheet?

I assume I have to store the value of D2 from the original sheet before copying and then retrieve it for use in the new sheet... Yes?

 

[Momentman]

You can have this before creating a copy

DateD2 = ActiveSheet.Range("D2").Value

Then after creating a copy of the sheet

NewWks.Range("D2").Value = DateD2 + 7

 

[steve.waye]

I'm getting an error.

Have I got the code correct?

Sub NewWorksheet()
    Dim DateD2, NewWks As Worksheet
    DateD2 = ActiveSheet.Range("D2").Value
    ActiveSheet.Copy after:=ActiveSheet
    Set NewWks = ActiveSheet
    NewWks.Range("D2").Value = DateD2 + 7
    ActiveSheet.Name = Format(Range("D2"), "dd-mmm-yy")
End Sub

 

[steve.waye]

Actually your code works perfect. Thanks.

I tried to be clever by protecting cell D2 but now the code cannot write to the protected cell.

Unless there is a way to unprotect the cell and protect it again as part of the process.

There is also some data in the original sheet that needs deleting in the new sheet. Is there a delete range method?

 

[steve.waye]

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.