Sheet naming using String Variable

KWilkinson

New Member
Joined
Dec 9, 2016
Messages
11
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,708
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
Check your spelling of sheetname
 

KWilkinson

New Member
Joined
Dec 9, 2016
Messages
11
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

New Member
Joined
Dec 9, 2016
Messages
11
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
This:

Code:
Cells(2, "B").Value
is essentially

Code:
Activesheet.Cells(2, "B").Value
not

Code:
Worksheets("Proposed").Cells(2, "B").Value
So it depends what is the activesheet at the time the line executes.
 

KWilkinson

New Member
Joined
Dec 9, 2016
Messages
11
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.. :eek:(
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Then you need to qualify your range as i pointed out. You cant use:

Code:
Cells(2, "B").Value
You need to use

Code:
Sheets("Proposed").Cells(2, "B").Value
or more fully:

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

KWilkinson

New Member
Joined
Dec 9, 2016
Messages
11
Okay, i understand now what you are saying. I need to set my active worksheet first then run the concatenation code.

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.
 
Last edited:

Forum statistics

Threads
1,082,305
Messages
5,364,406
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top