Renaming sheets to value from cell X depending on value on cell Y on the same row

Question

My goal is to create a new worksheet and name it depending on a value in a drop down list and loop it for every "yes" in the list.

The last string is not working and I have no idea how to loop the renaming process to fetch the correct name.

For example: If cell I65 has the value of "AP" I want the newly generated sheet to be named to the value in A65.

Dim rng As Range, cell As Range, was As Worksheet

Set rng = Range("A1:A3")
Sheets("Setup").Select
For Each cell In Range("I48:I85")
    If cell = "AP" Then
        Sheets("AP").Select
        Sheets("AP").Copy Before:=Sheets(1)
        Dim AP(2) As Worksheet
        Set AP(2) = ActiveWorkbook.Sheets("AP (2)")
        AP(2).Name = Worksheets("Setup").Range("A48:A85").Value
    End If
Next cell

score 0 · Answer 1 · answered Nov 01 '21 at 08:22

Though I did not fully understand what you are trying to do, This statement looks suspicious: AP(2).Name = Worksheets("Setup").Range("A48:A85").Value you are trying to rename a worksheet with a range of values? -not allowed. use a single cell like this or concatenate the values in the range before you remane. AP(2).Name = Worksheets("Setup").Range("A48").Value

score 0 · Answer 2 · answered Nov 01 '21 at 08:32

Have a look to the following code.

(1) Range("A48:A85").Value returns an array of values which cannot be used as a name. I have used the Offset-function to get the cell in col A of the same row.

(2) No need to use Select. Read (and understand) How to avoid using Select in Excel VBA

(3) Always qualify all worksheets, ranges and so on. With other words: Tell VBA which worksheet from which workbook you are using.

(4) I have used ActiveWorkbook in the With-clause, but I don't like it. Make up your mind on which workbook you are working. If it is the workbook the code lives, use ThisWorkbook instead. It it is another workbook, it's better to assign it to a workbook variable (eg when you open it). Especially in larger project, don't rely on Activeworkbook.

(5) You are declaring your variable AP as array with 2 elements. I guess you just wanted to name the variable similar to the new created sheet, but using an array and writing AP(2) is highly confusing. Just name it AP2 (or newWs or APCopy)

Dim ws As Worksheet, cell As Range

With ActiveWorkbook   ' <-- Better change that.
    Set ws = .Sheets("Setup")
    For Each cell In ws.Range("I48:I85")
        If cell = "AP" Then
            .Sheets("AP").Copy Before:=.Sheets(1)
            Dim AP2 As Worksheet
            Set AP2 = .Sheets("AP (2)")
            AP2.Name = cell.Offset(0, -8).Value
        End If
    Next cell
End With

Renaming sheets to value from cell X depending on value on cell Y on the same row

2 Answers2