How to make Sheet1 the same as a Cell in Excel

Robj241

Hi,

I have a spreadsheet on Excel and I am wanting it to copy the cell information ie a date - 01.01.18 to replace the Sheet tab at the bottom.


Example


Cell E4 '01.01.18' needs to make sheet1 read '01.01.18'

Many thanks for any help.

neilmcl

You can do it via VBA.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("E4")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

DoaM

It's been a long time since I've done any VBA, but ... why the 2nd Exit Sub? Doesn't End Sub do the same thing?

neilmcl

DoaM wrote: »

It's been a long time since I've done any VBA, but ... why the 2nd Exit Sub? Doesn't End Sub do the same thing?

There is a difference but your right it doesn't need to be there, bad cut & paste.

Robj241

Thanks for the info.

So do I highlight the cell E3
Click on Macro - Visual Basic Editor
Click the drop down menu and select Worksheet
Type in the info provided
Then save?

Many thanks

GunJack

...or just right-click on the tab you want, and select "rename" ?

ChrisK....._3

Thing is you need some sort of event to trigger the renaming and If you are happy to right click then just
Right Click Cell/Copy
Right Click Tab/Rename/CTRL+V

The problem with the macro is that it renames the Tab EVERY TIME you select a cell (event trigger) for ever and ever amen

neilmcl

Robj241 wrote: »

Thanks for the info.

So do I highlight the cell E3
Click on Macro - Visual Basic Editor
Click the drop down menu and select Worksheet
Type in the info provided
Then save?

Many thanks

Just right-click the sheet name, select View Code and copy the code I provided into the VBA editor that pops up.. Click Save and exit the VBA editor and away you go. The excel doc, however, must be saved as a Macro enabled worksheet (*.xlsm).

Robj241

Unfortunately this comes up as an error.

The Cell E3 contains a date - 01/01/18 but it won't allow /

Is there a way round this?

Many thanks for your assistance.

rmg1

If you don't want the code to run every time you change cells (which will be annoying) then try this:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$4" Then
Application.ActiveSheet.Name = VBA.Left(Target, 31)
End If
End Sub

It will fire every time you change a cell but won't do anything unless you're in cell E4.

neilmcl

Robj241 wrote: »

Unfortunately this comes up as an error.

The Cell E3 contains a date - 01/01/18 but it won't allow /

Is there a way round this?

Many thanks for your assistance.

Could always convert the date to something like 01 Jan 2018.