CODEWITHSUNDEEP
excelvba
JoDi2016
JoDi2016

Reputation: 1

Renaming new sheets

I'm trying to generate letters by automatically adding the letterhead information to a letter template.

I managed to get the VBA working for copying a template for each record in input sheet and populating each newly generated mail with data from input sheet.

I'm struggling with the task to rename the newly added sheets. New name should equal last name from input sheet.

Similar threads regarding renaming sheets didn't point me into the right direction.

Sub getdata()

' 1. Copy template for each record in input sheet

Dim no_sheets As Integer

no_sheets = Sheets("Input").Range("N2").Value

For sheet_index = 1 To no_sheets
    ThisWorkbook.Sheets("Template").Copy _
    After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next sheet_index

' 2.  Rename copies of template

Dim sheet_name As String
sheet_name = Sheets("Input").Range("B" & (sheet_index + 1)).Value

' 3. Populate each sheet with the data from the Input sheet

For sheet_index = 2 To no_sheets + 1

    ' Letter head row 1
    Sheets("Template (" & sheet_index & ")").Range("B8") = Sheets("Input").Range("H" & sheet_index)

    ' ...

Next sheet_index

End Sub

Upvotes: 0

Views: 40

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

It would be easier to use a single loop:

Sub getdata()

    Dim no_sheets As Long, sheet_index As Long

    no_sheets = Sheets("Input").Range("N2").Value

    For sheet_index = 1 To no_sheets
        ThisWorkbook.Sheets("Template").Copy _
        After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

        'rename/populate the sheet you just created
        With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

              .Name = Sheets("Input").Range("B" & (sheet_index + 1)).Value

              'etc etc

        End With

    Next sheet_index

End Sub

Upvotes: 1

Related Questions