VBA, Renaming Excelsheets to value from cell X depending on value on cell Y on the same row-CodePudding

Im not too experienced with VBA but I recently recieved the responsibility to develop an exstensive Excel-document for the company I work for. My goal is to create a macro that will create a new worksheet and rename it depending on a value in a drop down list and loop it for every "yes" in the list. This is what I currently got. The last string is not working and I have no idea how to loop the renaming process and make sure it fetches 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. Thank you in advance.

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

CodePudding user response：

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

CodePudding user response：

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