CODEWITHSUNDEEP
vbaexcel
Navkanth
Navkanth

Reputation: 50

How to add strings while naming a sheet in VBA?

I was able to do it in a macro with the below line,

Worksheets.Add(after:=Worksheets(current)).Name = Format(Date, "ddmmyy") + " deciles"

But a similar line is not working in a different macro

Worksheets.Add.Name = "Performance classification" + Format(Date, "ddmmyy")

I am getting the runtime error 1004 - application-defined or object defined error.

Upvotes: 0

Views: 150

Answers (2)

Chris Slade
Chris Slade

Reputation: 309

Try

Worksheets.Add.Name = "Perform class " & Format(Date, "ddmmyy")

If you use "Performance classification" the name will be too long, > 31 characters.

The expression works with and without the parenthses too.

Upvotes: 1

Mike Monteiro
Mike Monteiro

Reputation: 1457

Try: Worksheets.Add().Name = "Performance classification" + Format(Date, "ddmmyy")

In the line of code that works, Worksheets.Add is a method that you're calling to create and return a new worksheet. Then you're invoking the Name property and setting it to a new value. VBA needs you to use parentheses on the Add method to denote that you care about the return value (the new worksheet object)

Upvotes: 2

Related Questions