mulan571
06-03-2016, 09:13 AM
Hello :hi:
I created a vba so I can select several files to print into separate pdf forms and automatically save them to a specific folder. Is there a way I can code it differently so another person can use the file and have the ability to change the file path and still able to print in batches and keep the file names?
Here is a copy of the code I'm using:
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("2016 VRI Form")
Set Form4T = Worksheets("2016 4T Form")
Set FormSC = Worksheets("2016 SC Form")
Set DataWks = Worksheets("2016 VRI Data")
myCustomer = Array("A6")
With DataWks
'first row of data to last row of data in column D
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:="\\muthr\sales\Volume Rebate Calculator\VR Calculator\2016 VRI Reports\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:="\\muthr\sales\Volume Rebate Calculator\VR Calculator\2016 VRI Reports\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
'after testing, change to Preview to False to Print
FormWks.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\muthr\sales\Volume Rebate Calculator\VR Calculator\2016 VRI Reports\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
Any help would be greatly appreciated! :beg:
I created a vba so I can select several files to print into separate pdf forms and automatically save them to a specific folder. Is there a way I can code it differently so another person can use the file and have the ability to change the file path and still able to print in batches and keep the file names?
Here is a copy of the code I'm using:
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("2016 VRI Form")
Set Form4T = Worksheets("2016 4T Form")
Set FormSC = Worksheets("2016 SC Form")
Set DataWks = Worksheets("2016 VRI Data")
myCustomer = Array("A6")
With DataWks
'first row of data to last row of data in column D
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:="\\muthr\sales\Volume Rebate Calculator\VR Calculator\2016 VRI Reports\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:="\\muthr\sales\Volume Rebate Calculator\VR Calculator\2016 VRI Reports\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
'after testing, change to Preview to False to Print
FormWks.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\muthr\sales\Volume Rebate Calculator\VR Calculator\2016 VRI Reports\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
Any help would be greatly appreciated! :beg: