Auto Define a sheet name

Question

I am trying to auto-define a sheet name in VBA.

This is the code I've created:

Range("F7:F11").Select
Selection.Copy
Sheets("janeiro").Select
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Basically, I need the contents in cell b2 to define the name of the sheet. On this example, the actual name of of the sheet is "janeiro", but I need the name to be defined by the contents of cell b2.

For example, February, March... done in an automatic way.

Is this possible?

There are some gotchas with naming sheets. I wrote a [blog post](http://christopherjmcclellan.wordpress.com/2013/10/25/dynamically-naming-excel-worksheets-the-headache-free-way/) about it you might find interesting. — RubberDuck, May 23 '14 at 15:45

score 3 · Answer 1 · edited May 23 '17 at 12:05

Replace "janeiro" with Range("B2").Value.

Then, tighten up your code a bit to avoid using Select methods:

Range("F7:F11").Copy
Sheets(Range("B2").Value).Range("F7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Note: this will raise an error if the specified sheetname doesn't exist.

OR... this would be a great opportunity to show you how to use variables, rather than explicit references:

Dim shName as String 'Defines a STRING variable which we will use to hold a sheet name

shName = Range("B2").Value

'## Now, we can simply refer to shName any time we need to refer to the value in "B2"

Range("F7:F11").Copy
Sheets(shName).Value).Range("F7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Auto Define a sheet name

1 Answers1