CODEWITHSUNDEEP
vbaexcel
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Naming inserted worksheet like another worksheet

I want to add a new sheet in a workbook and name it exactly like the active worksheet but with a ' after it. So if the name of the active sheet is 0908 then I want the name of the newly inserted worksheet to be 0908'.

I searched a bit and I saw how to name a sheet with a reference to somewhere else, but only for values inside other worksheets.

That's the code for referencing inside other worksheets.

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

Upvotes: 0

Views: 39

Answers (3)

Tony Wilson
Tony Wilson

Reputation: 44

As you have to have a character after the " ' ", you could use

Worksheets.Add.Name = ActiveSheet.Name & "' "

Adding the space after the ' would give the effect that you were looking for.

Upvotes: 1

Variatus
Variatus

Reputation: 14383

You might be looking for code like this.

Dim WsName As String
Dim Ws As Worksheet

WsName = ActiveSheet.Name
Set Ws = Worksheets.Add(After:=Worksheet(Worksheets.Count))
Ws.Name = WsName & "1"

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

Something like that should work.

Worksheets.add.Name = ActiveSheet.Name & "xxx"

You just need to read the name of the active sheet and append a string to it.

But an apostrophe at the end of a sheet doesn't work!
This is only allowed if it is followed by another character like:

Worksheets.add.Name = ActiveSheet.Name & "'s"

Upvotes: 2

Related Questions