CODEWITHSUNDEEP
vbaexcel
user3669051
user3669051

Reputation: 21

Auto Define a sheet name

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?

Upvotes: 2

Views: 93

Answers (1)

David Zemens
David Zemens

Reputation: 53663

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

Upvotes: 3

Related Questions