Sheet naming using String Variable

[KWilkinson]

Hi all,

I'm fairly new to VBA and trying to write some basic macros to reduce some drudgery when working with a few of our standard spreadsheets. As part of this I'm trying to create a new sheet which needs to be named based on data in 2 separate cells.

The code is pretty basis but I can see what I'm doing wrong when it comes to naming the sheet - can anyone assist and point me in the right direction?

Sub CreateNewSheet()
Dim Sheetname As String
Dim ws As Worksheet
Sheeetname = Worksheets("Proposed").Cells(2, "A") & "-" & Cells(2, "B").Value


Set ws = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets("Proposed"))
ws.Name = Sheetname


End Sub

 

[jimrward]

Check your spelling of sheetname

 

[KWilkinson]

Thanks, I still get a runtime error '1004' Method 'Name' of object'_Worksheet' failed...

I'm also finding when debugging the script that the Sheetname value works sometimes but not others - in some instances it's not picking up the value in cell B2...

 

[KWilkinson]

Okay - first issue is because I've called the variable Sheetname - If I change this to x it works fine.

The concatenation though isn't working reliably - can anyone tell me what is wrong with this or perhaps a better way of doing it?

 

[steve the fish]

This:

Cells(2, "B").Value

is essentially

Activesheet.Cells(2, "B").Value

not

Worksheets("Proposed").Cells(2, "B").Value

So it depends what is the activesheet at the time the line executes.

 

[KWilkinson]

Thanks for the reply. The issue I'm having is that this line isn't concatenating correctly.

In Cell A2 I have 2017-08-04 and in B2 I have P01 the result I'm after is 2017-08-04-P01 what I'm getting is 2017-08-04-

Sometimes it seems to work and others it doesn't... at the moment it very much doesn't.. (

 

[steve the fish]

Then you need to qualify your range as i pointed out. You cant use:

Cells(2, "B").Value

You need to use

Sheets("Proposed").Cells(2, "B").Value

or more fully:

Sheetname = Sheets("Proposed").Cells(2, "A") & "-" & Sheets("Proposed").Cells(2, "B").Value

 

[KWilkinson]

Okay, i understand now what you are saying. I need to set my active worksheet first then run the concatenation code.

Sub CreateNewSheet()
Dim x As String
Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets("Proposed"))
    Worksheets("Proposed").Activate
    x = Cells(2, "A") & "-" & Cells(2, "B").Value
    ws.Name = x
    
End Sub

This code now works as expected. Thanks everyone for your time, much appreciated.