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:

Col A	Col B	Col C	Col D	Col E
Form type	print	agency	rep	customer
		ABC	ST	XYZ INC
4T		DEF	BH	ZYXW CORP
SC		GHI	CR	RUST INC
		I4I	TH	NATIONAL INC
SC		MTW	AC	PLASTICS INC

<tbody>
</tbody>

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