Renaming sheet in Excel with reference to another sheet, VBA

Question

I would like to rename my newly added sheet, by referencing to a value of a cell that is in another sheet. It looks simple, but it doesn't work.

Sheets("TABLAK").Select
Sheets.Add
Dim ujfulneve As String
ujfulneve = Sheets("MENU").Range("B2").Value
Sheets(ActiveSheet).Name = ufjulneve

It writes type mismatch error for the last row. Can you help?

`ActiveSheet.Name = ufjulneve` and you don't need the initial `Sheets("TABLAK").Select` at all — user3598756, Sep 17 '16 at 12:01
Thank you. I just wrote that line, because of the positioning of the new sheet. — Máté Brunner, Sep 18 '16 at 12:01

score 6 · Answer 1 · answered Sep 17 '16 at 13:21

6

accepting Socii challenge about maximum code semplification the shortest code is:

Worksheets.add.Name = Worksheets("MENU").Range("B2").value

answered Sep 17 '16 at 13:21

user3598756

28,893
4
18
28

I do like a good one-liner. Never realised you could set the Name directly in the Worksheets.Add function. – Socii Sep 17 '16 at 13:56
1

shortest is `Sheets.Add.Name = [MENU!B2]` – Slai Sep 17 '16 at 15:14
@Slai +1 for the shortest. Plus that's a great illustration of the type of uncommon notation I was thinking of in my answer when I referred to one-liners sometimes being hard for many people to understand. – Jaquez Sep 17 '16 at 18:35
Thank you! And if i want to add some plus character to the b2's value? For example b2=abc, and i want to be it abcdef – Máté Brunner Sep 18 '16 at 12:12
Nothing, i figured out:) – Máté Brunner Sep 18 '16 at 12:18
@MátéBrunner, sorry I only see your comments now. But happy to see you already made it by yourself. BTW, if you used my solution you may want to mark my answer as accepted! – user3598756 Sep 18 '16 at 13:45
I haven't enough reputations to rate:) I'm new here. – Máté Brunner Sep 18 '16 at 14:17
@MátéBrunner, no reputation needed to accept an answer to a question of yours, just [click on the check mark beside the answer to toggle it from greyed out to filled in](http://stackoverflow.com/help/someone-answers) – user3598756 Sep 18 '16 at 14:37
@MátéBrunner: not! :-( – user3598756 Sep 19 '16 at 07:22

score 4 · Answer 2 · edited May 23 '17 at 10:34

4

The code can be simplified a lot by using a With block:

With Sheets.Add
    .Name = Sheets("MENU").Range("B2").Value
End With

See this post for further reading and a few more methods of adding a new Worksheet: How to Add a Named Sheet at the end of all excel sheets

edited May 23 '17 at 10:34

Community

1
1

answered Sep 17 '16 at 13:11

Socii

535
4
13

This is an _Excel_ lent answer because it: 1)answers the question, 2)is terse, 3)stands independently, and 4)**Provides a link to a duplicate**, or in this case a related post. – ElderDelp Jul 28 '17 at 17:55

Jaquez · Accepted Answer · 2016-09-17T18:30:10.830

The other answers are correct and efficient but I wanted to add a little explanation as to what is going wrong with your code and why their code is the way to go. One-liners can often seem mysterious and daunting as if they are too clever for their own good so understanding them well enough to use them effectively is essential to good coding.

The type mismatch error is due to the fact that the Sheets() function is expecting a String (the name of the sheet) but you are a passing it ActiveSheet which is a Sheet object. Since it is already a Sheet, you can just use that and change its Name property directly:

ActiveSheet.Name = ufjulneve

But even better than that, Excel is very slow when it has to mimic the typical user interaction that you would take doing the same thing manually like selecting the worksheet before you reference it. You should remove the following line altogether

Sheets("TABLAK").Select

because you don't use that worksheet after you select it anyway. In addition, upon skimming through the code later, if you don't see/realize that Sheets.Add automatically selects the newly created worksheet you may the get the incorrect impression that Sheets("TABLAK") is the ActiveSheet since it was just selected 4 lines earlier.

Finally, unless you need to reference the value of Sheets("MENU").Range("B2").Value again elsewhere, you might as well remove it as suggested by the others, saving an additional two lines, leaving

Worksheets.Add
ActiveSheet.Name = Worksheets("MENU").Range("B2").Value

Since this still refers to ActiveSheet, making it fagile in the face of later changes (what happens later if you mistakenly add another line between those two that changes the sheet selection?) then the one-liner is by far the best way to go:

Worksheets.Add.Name = Worksheets("MENU").Range("B2").Value

Renaming sheet in Excel with reference to another sheet, VBA

3 Answers3