CODEWITHSUNDEEP
vbaexcel
Máté Brunner
Máté Brunner

Reputation: 97

Renaming sheet in Excel with reference to another sheet, VBA

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?

Upvotes: 2

Views: 3206

Answers (3)

Jaquez
Jaquez

Reputation: 920

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

Upvotes: 3

user3598756
user3598756

Reputation: 29421

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

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

Upvotes: 6

Socii
Socii

Reputation: 545

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

Upvotes: 4

Related Questions