Generating documents by template using VBA

Question

I am currently working on a project, that consists generating various document examples in a new workbook, basing on the table data and document template. Here's what I have tried:

Sub CopyData()
    Sheets("Staff").Select

    For i = 2 To 100000
        If Cells(i, 1).Value = "" Then
            i = 100000
            Exit For
        End If

        Sheets("TEMPLATE_TARGET").Select

        Range("Name").Value = Sheets("Staff").Cells(i, 1).Value & " " & _
        Range("Personalcode").Value = Sheets("Staff").Cells(i, 3).Value
        Range("Residence").Value = Sheets("Staff").Cells(i, 4).Value
        Range("Job").Value = Sheets("Staff").Cells(i, 5).Value
        Cells.copy
        Selection.Copy

        Worksheets.Add.Name = Sheets("Staff").Cells(i, 1).Value

        Application.DisplayAlerts = False
        ActiveSheet.Paste
        Application.CutCopyMode = False

        Sheets("Staff").Select
    Next i

    MsgBox ("YAY")
End Sub

Unfortunately, my code's executing stops in the middle of process, claiming that the worksheet("Staff") is already existing and therefore - is out of range. How do you recommend to indicate the option to generate the final results as the new workbook to fix this issue? Thanks in advance.

Possible duplicate of [Generate Word Documents (in Excel VBA) from a series of Document Templates](https://stackoverflow.com/questions/5106743/generate-word-documents-in-excel-vba-from-a-series-of-document-templates) — Our Man in Bananas, Jul 26 '18 at 09:45
@jūlija-Žarnauska: have a look at [Generate Word Documents (in Excel VBA) from a series of Document Templates](https://stackoverflow.com/questions/5106743/generate-word-documents-in-excel-vba-from-a-series-of-document-templates) — Our Man in Bananas, Jul 26 '18 at 09:46
I've got the run-time error message 1004 on the property: Worksheets.Add.Name=Sheets("Staff") and I'm afraid that renaming the sheet isn't the proper solution. — Jūlija Žarnauska, Jul 26 '18 at 10:35

score 3 · Accepted Answer · answered Jul 26 '18 at 10:55

3

Try this:

Declare a variable As Worksheet and then set it to the add method.

Dim oWorkSheet As worksheet

Set oWorkSheet = ThisWorkbook.Sheets.Add(, , 1, xlWorksheet)
oWorkSheet.Name = "nameOfYourSheet" 'must be different from the others sheets.

answered Jul 26 '18 at 10:55

Jérémy Gamba

70
1
10

score 1 · Answer 2 · edited Jul 26 '18 at 12:35

1

Replace

Worksheets.Add.Name = Sheets("Staff").Cells(i, 1).Value

with

With Worksheets.Add
    .Name = Sheets("Staff").Cells(i, 1).Value
End With

edited Jul 26 '18 at 12:35

Pᴇʜ

56,719
10
49
73

answered Jul 26 '18 at 09:43

PaichengWu

2,649
1
14
28

Generating documents by template using VBA

2 Answers2