Using Variables as sheet names in VBA (Sheets.Add)

[kotzus]

Hi,

Trying to use a variable string as the name in a sheet.

Public mon As String (mon is "4")
c1 = """"
c2 = c1 + mon + c1
Sheets.Add after:=Worksheets(c2)


c2 has the value of "4"

This is not working. Probably because all of it is the string and the Function " is missing

I would be obliged if you could assist me

Regards

 

[6StringJazzer]

You haven't really explained what you want to do in terms of starting conditions and desired result.

What does "not working" mean? What do you want to happen and what happens instead?

This code will add a new sheet after the existing sheet named "4".

Other than that I don't know what your question is.

 

[Micron]

Public mon As String (mon is "4")

That cannot be what you have. Post the exact procedure and if you want to include information, use comments like this

Public mon As String()
c2 = c2 + 1 'this is a comment about c2
End Sub

and use code tags so that the code maintains its indentation.

Also explain what you are trying to achieve. You should also always declare your variables if for no other reason that others won't have to guess what they represent. I always enforce variable declaration with Option Explicit.

 

[kotzus]

Dear all,
thank you for your suggestions

I need to create a sheet name based on a variable
e.g. sheet name should be 4. String "4" is being taken from a variable with the name mon.
the line should look like Sheets.Add after:=Worksheets("4")

Public Sub Month_Click()

mon = goMonth.Value

Dim c1, c2 As String
c1 = """"
c2 = c1 + mon + c1

Sheets.Add after:=Worksheets(c2)

Result: Subscript out of range

 

[deadlyduck]

I think you're code is trying to add a new worksheet after one that doesn't exist! The name in brackets should be the sheet you want the new one to come after but if I'm understanding you correctly, it looks like you want a new sheet named 4 to come after a non-existent sheet also named 4.

 

[6StringJazzer]

If you want to create a new worksheet as the last worksheet and name it "4" you need to do this:

Dim NewSheet As Worksheet
Set NewSheet = Sheets.Add (after:=Worksheets(Worksheets.Count))
NewSheet.Name = c2

 

[kotzus]

Hi and thank you for the reply.

Workbooks("IC1.xlsm").Activate
  
Sheets.Add after:=Worksheets(c2)

The last worksheet has the name "Result10" I have already 2 sheets in my book

Still I think there must be a problem with the created string c2 and the interpretation in the Sheets.Add

 

[6StringJazzer]

Read post #6 for the solution.

Your original line of code

Sheets.Add after:=Worksheets(c2)

does NOT add a worksheet named 4. It adds a new worksheet AFTER the worksheet named 4. Except there is no worksheet named 4, so you get an error.

 

[kotzus]

ty very much, solved. My mistake