VBA creating multiple different excel document from one list

Ann1611

New Member
Joined
Nov 27, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!!



VBA Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Watch MrExcel Video

Forum statistics

Threads
1,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top