Automatically rename a sheet based on a cell

Question

Hello,
I am looking to automatically rename a sheet based on a cell that is located on that sheet in C6. I have a hundred sheets in the same workbook that I would like to rename automatically, each time based on the C6 cell whose text changes from one sheet to another.
I don’t know anything about macros or VBE, etc., and if this formula exists, it would be a great help!
I am working on Excel 2011 in English, version 14.6.6, on Mac.
Thank you!

Configuration: Mac OS X (10.11.5) / Chrome 54.0.2840.98

Zoul67 · Answer

Hello,

Impossible by formula, but definitely by macro...
Press Alt+F11, in the VBA project explorer (on the left), insert a module and paste:

Sub rename() Dim sh As Worksheet For Each sh In Worksheets sh.Name = sh.Range("C6") Next End Sub

Place your cursor in this code and press the Play button.

See you later!

The_boss_68 · Answer

Good evening,

Try to place this macro in ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) 'if c6 is manually filled On Error Resume Next Sh.Name = Sh.[c6] End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 'if c6 contains a formula On Error Resume Next Sh.Name = Sh.[c6] End Sub

Best regards

The_boss_68 · Answer

Good evening
For a contributor to assist you, it would be advisable for you to provide an anonymized file

1) Go to https://www.cjoint.com/
2) Click on [Browse] to select your file
3) Scroll to the bottom of the page to click on [Create Cjoint link]
4) After a few seconds, the second page will appear, with the link
underlined in blue; select it and click "Copy"
5) Return to your discussion on commencamarche, and in your reply message, click "Paste".
Best regards

Zoul67 · Answer

Hello,

That's exactly what I thought... Your question doesn't quite match your need (for example, it wasn't initially about duplicating the sheets; I assumed they were all already created).
I believe you have your "SEM" sheet that contains all the information. My idea is to:
- first, create a "Template" tab whose formulas work regardless of the value in cell C6;
- then duplicate that "Template" tab via a macro and give the appropriate name to each tab and fill in cell C6.

Here's my proposal: https://www.cjoint.com/c/FLhlmI3iMrJ
You just need to press the button to create all the tabs.

See you later.

Automatically rename a sheet based on a cell

4 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.