Hi Johan,

Am Mon, 27 May 2019 08:09:44 -0700 (PDT) schrieb JS SL:

Sheet1:
Column A exist "Y or N" if this record should be copied to the templatesheet for saving/print as pdf for this specific record.

If Yes in column A; (record 2 till last used);

1a) then.. copy for this specific record the data in Sheet1 ColumnD till Z to sheet2 datafield D4 with a 'transpose' action (you then get the data in Sheet2 in Column D4 till D26).

1b) Then also copy for this specific record the data in Sheet1 Column BB till BD to Sheet2 datafield D30 with a 'transpose' action (you get tje data in sheet2 in Column D30 till D33).

2a) then.. save/print the file as pdf with the filename as mentioned in Sheet1 column B of the same record (could be like 'Template-Blabla' - becomes then 'Template-Blabla.pdf').

2b) The save/print the file should be saved to the directory as mentioned in Sheet1 Column C of the same record (could be like 'C:/Apps/Templates').

try:

Sub SaveAsPDF()
Dim varCrit As Variant, varData1 As Variant, varData2 As Variant
Dim LRow As Long, i As Long
Dim dest1 As Range, dest2 As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varCrit = .Range("A2:C" & LRow)
varData1 = .Range(.Cells(2, "D"), .Cells(LRow, "Z"))
varData2 = .Range(.Cells(2, "BB"), .Cells(LRow, "BD"))
End With
With Sheets("Sheet2")
Set dest1 = .Range("D4"): Set dest2 = .Range("D30")
For i = LBound(varCrit) To UBound(varCrit)
If varCrit(i, 1) = "Yes" Then
dest1.Resize(UBound(varData1, 2)) = _
Application.Transpose(Application.Index(varData1, i, 0))
dest2.Resize(UBound(varData2, 2)) = _
Application.Transpose(Application.Index(varData2, i, 0))
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=varCrit(i, 3) & _
varCrit(i, 2) & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016