CODEWITHSUNDEEP
excelvba
Jason
Jason

Reputation: 7682

Add sheets and rename in loop

I have a "template" sheet that I copy in a loop (number of values in a column in another sheet) and then I use that value in the column to rename the sheet.

I unhide the template sheet, copy after, and then rename the sheet using the Index plus the loop index i.
This approach is dependent on the template sheet always being in the same location, so the other sheets can be inserted after and the index is correct.

I'd like, on each iteration of the loop, the copied sheet added as the last sheet and then renamed, so that it is not dependent on the "template" index value (in case a user inserts a sheet after the "template").

Sub AddMultipleSheet2()

Dim sheets_count As Integer
Dim sheet_name As String
Dim i As Integer

Worksheets("Template").Visible = True
sheet_count = Range("B12:B61").Rows.Count

For i = 1 To sheet_count

    sheet_name = Sheets("Test setup").Range("B12:B61").Cells(i, 1).Value
    
    If SheetCheck(sheet_name) = False And sheet_name <> "" Then
        'Copy sheet
        Set ws = Sheets("FAR 1")
        ws.Copy After:=Sheets(Sheets.Count)
        'Rename sheet (name of test step)
        Set wsNew = Sheets(Sheets("Template").Index + i)
        wsNew.Name = sheet_name
        'Set value in new sheet
        Set myCellSetValue = ThisWorkbook.Worksheets(sheet_name).Range("A1")
 
        'set cell value with Range.Value property
        myCellSetValue.Value = sheet_name
    
    End If

Next i

Worksheets("Template").Visible = False
End Sub

Upvotes: 0

Views: 492

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

You put the copy after the last sheet, so you can pick it up from there:

Set wsNew = Sheets(Sheets.Count)

Upvotes: 2

Related Questions