vba code to batch print forms and save as pdf into specific folders

[mulan571]

Hi,

I created a code to batch print forms, save as pdfs into 1 specific folder (Individual PDF Files)

Form4T.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\sales\Individual PDF Files\" & myCell.Value & " " & Format(Date, "mm-dd-yyyy")

I would like to break it down further so the path file will change to something like this:

"\\sales\" & Agency.Value & "\" & Rep.Value & "\" & myCell.Value & " " & Format(Date, "mm-dd-yyyy")

The both the agency & reps are variables just like myCell.

Here is a copy of my code that prints these forms:

Sub PrintUsingDatabase()    Dim FormWks As Worksheet
    Dim DataWks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    Dim iCtr As Long
    Dim myCustomer As Variant

    Set FormWks = Worksheets("Standard Form")
    Set Form4T = Worksheets("4T Form")
    Set FormSC = Worksheets("SC Form")
    Set DataWks = Worksheets("Data")


    myCustomer = Array("A6")


    With DataWks

        Set myRng = .Range("E5", .Cells(.Rows.Count, "E").End(xlUp))
    End With


    For Each myCell In myRng.Cells
        With myCell
                'if the row is not marked, do nothing
            If IsEmpty(.Offset(0, -3)) Then
                
                'if print 4 tier customer
            ElseIf InStr(.Offset(0, -4), "4T") Then
                .Offset(0, -3).ClearContents 'clear mark for the next time
                For iCtr = LBound(myCustomer) To UBound(myCustomer)
                    Form4T.Range(myCustomer(iCtr)).Value _
                        = myCell.Offset(0, iCtr).Value
                Next iCtr
                Application.Calculate


                Form4T.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\sales\Individual PDF Files\" & myCell.Value & " " & Format(Date, "mm-dd-yyyy")
                
                lOrders = lOrders + 1
                
                'if print Special Customer
            ElseIf InStr(.Offset(0, -4), "SC") Then
                .Offset(0, -3).ClearContents 'clear makr for the next time
                For iCtr = LBound(myCustomer) To UBound(myCustomer)
                    FormSC.Range(myCustomer(iCtr)).Value _
                        = myCell.Offset(0, iCtr).Value
                Next iCtr
                Application.Calculate
                FormSC.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\sales\Individual PDF Files\" & myCell.Value & " " & Format(Date, "mm-dd-yyyy")
                
                lOrders = lOrders + 1
                
                'print for standard VRI form
            Else
                .Offset(0, -3).ClearContents 'clear mark for the next time
                For iCtr = LBound(myCustomer) To UBound(myCustomer)
                    FormWks.Range(myCustomer(iCtr)).Value _
                        = myCell.Offset(0, iCtr).Value
                Next iCtr
                Application.Calculate 'just in case
                 
                FormWks.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\sales\Individual PDF Files\" & myCell.Value & " " & Format(Date, "mm-dd-yyyy")
                
                lOrders = lOrders + 1
            End If
        End With
    Next myCell
    
    MsgBox lOrders & " orders were printed."
    
End Sub

Here's a sample of the data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD]Form type[/TD]
[TD]print[/TD]
[TD]agency[/TD]
[TD]rep[/TD]
[TD]customer[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]ST[/TD]
[TD]XYZ INC[/TD]
[/TR]
[TR]
[TD]4T[/TD]
[TD][/TD]
[TD]DEF[/TD]
[TD]BH[/TD]
[TD]ZYXW CORP[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD][/TD]
[TD]GHI[/TD]
[TD]CR[/TD]
[TD]RUST INC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]I4I[/TD]
[TD]TH[/TD]
[TD]NATIONAL INC[/TD]
[/TR]
[TR]
[TD]SC[/TD]
[TD][/TD]
[TD]MTW[/TD]
[TD]AC[/TD]
[TD]PLASTICS INC[/TD]
[/TR]
</tbody>[/TABLE]

Please please please... . I've been searching hi & low, trying all different types of codes and every try, my excel crashes.