CODEWITHSUNDEEP
vbaexcel
TonyP
TonyP

Reputation: 333

VBA Code to create and name new sheet based on variable

I am trying to get my code to add a sheet with the name the code determines based on the Function_Name variable. When I run this it names the new sheet Function_Name. Any thoughts?

For X = 1 To B
    Sheets("Calculations").Select
    Function_Name = Range("B2").Offset(X, 0)   'Gets the Function Name
    Worksheets.Add().Name = ("Function_Name")
Next X

Upvotes: 0

Views: 1706

Answers (3)

Raj Mohan
Raj Mohan

Reputation: 1

Dim wksSource   as worksheet

For X = 1 To B
    Sheets("Calculations").Select
    Function_Name = Range("B2").Offset(X, 0)   'Gets the Function Name
    Set wksSource = worksheets.add
    wksSource.Name = ("Function_Name")
    Set wksSource = nothing
Next X

Upvotes: 0

HedgePig
HedgePig

Reputation: 468

In your original code, you have Function_Name in quotes in

Worksheets.Add().Name = ("Function_Name")

The quotes should be removed.

Tim's answer gives an even more succinct way of writing the code.

You may also consider putting in some error trapping because if you try and add a worksheet with a name that already exists, you will get a run time error.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166196

For X = 1 To B
    Worksheets.Add().Name = Sheets("Calculations").Range("B2").Offset(X, 0).Value
Next X

Upvotes: 2

Related Questions