VBA creating multiple different excel document from one list

[Ann1611]

Hi everyone,
I need help, VBA code that I posed here is creating multiple (exactly the same) PDF files from one list, now I need to create (different) excel files instead of (exactly the same) PDF files and each excel should be automatically filled with data from list (each row has data that created excel should contain).
I have 5 different templates and my VBA should create excel document according to condition in one column, i.e. if the column says "Sales" VBA should create Sales template excel file and fill the cells with data of people.

Any help?

thank you!!

Public Function toArray(RNG As Range)
    Dim arr As Variant
    arr = RNG

    With WorksheetFunction
        If UBound(arr, 2) > 1 Then
            toArray = Join((.Index(arr, 1, 0)), ";")
        Else
            toArray = Join(.Transpose(.Index(arr, 0, 1)), ";")
        End If
    End With
End Function

Sub SetStore(folderPath As String)
If Dir(folderPath, vbDirectory) = "" Then
    MkDir Path:=folderPath
End If
End Sub

Sub CreateDocuments()

Application.ScreenUpdating = False


Sheets("zaposlenici").Select

Dim kadBroj() As String, naziv() As String, rm() As String, firstContract() As String, datefrom() As String, dateto() As String, dateofbirth() As String, placeofbirth() As String, TM() As String, nameTM() As String
Dim lastRow As Integer, i As Integer


lastRow = Range("A:A").Cells(Range("A:A").Rows.Count, "A").End(xlUp).Row

TM = Split(toArray(Range("A2:A" & lastRow)), ";")
nameTM = Split(toArray(Range("B2:B" & lastRow)), ";")
personnumber = Split(toArray(Range("C2:C" & lastRow)), ";")
number = Split(toArray(Range("D2:D" & lastRow)), ";")
rm = Split(toArray(Range("E2:E" & lastRow)), ";")
firstContract = Split(toArray(Range("F2:F" & lastRow)), ";")
dateFrom = Split(toArray(Range("G2:G" & lastRow)), ";")
dateTo = Split(toArray(Range("H2:H" & lastRow)), ";")
dateofbirth = Split(toArray(Range("I2:I" & lastRow)), ";")
placeofbirth = Split(toArray(Range("J2:J" & lastRow)), ";")


Sheets("list").Select


For i = 0 To (UBound(number, 1) - LBound(number, 1))
   
    Range("C2").Value = "Name and surname: " + naziv(i)
    Range("C3").Value = "Mat. number: " + kadBroj(i)
    Range("C4").Value = "Date and place of birth: " + dateofbirth(i) + ", " + placeofbirth(i)
    Range("C5").Value = "working position: " + rm(i)
    Range("C6").Value = "Contract: " + datefrom(i) + " - " + dateto(i)
    Range("C7").Value = "Date of : " + firstcontract(i)
    Range("D2").Value = "Store:  " + TM(i)
   
   
    functions.SetStore (ActiveWorkbook.Path + "\" + TM(i))
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ActiveWorkbook.Path + "\" + TM(i) + "\" + kadBroj(i) + "-" + naziv(i) + ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
   
Next i


Application.ScreenUpdating = True

End Sub