Print excel to PDF for rows of unique values

[Nathanoj]

Hi,

My code below currently copy/paste each row from Sheet 1 (detailsSheet) to Sheet 2 (reportSheet) and then print/save it as a PDF on my desktop, and works great!
As a next step (where I would appreciate some help), I would like to copy rows as long as the value in column A is the same. For example if there are 3 rows with the unique value of "Pile123" in column A , I want to copy those three rows to the reportSheet before saving the PDF to the desktop. But I might also have a value with "Pile456" which only consists of 1 row, in which case I only want it to copy the one row before saving. My current code is below:


Sub ExportingandSavingPDF()

'Defining worksheets
Dim detailsSheet As Worksheet
Dim reportSheet As Worksheet
Dim i As Long
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row

'Looping the through each row
For i = 2 To LastRow

Set reportSheet = ActiveWorkbook.Sheets("Contract Form")
Set detailsSheet = ActiveWorkbook.Sheets("New POs")

'Assigning values
SPile = detailsSheet.Cells(i, 2)
SClient = detailsSheet.Cells(i, 1)
SCommodity = detailsSheet.Cells(i, 2)
SOption = detailsSheet.Cells(i, 3)
SQtyMT = detailsSheet.Cells(i, 4)
SPriceMT = detailsSheet.Cells(i, 5)
SWhs = detailsSheet.Cells(i, 6)
SIncoterm = detailsSheet.Cells(i, 8)
SDeliveryCity = detailsSheet.Cells(i, 9)
SPO = detailsSheet.Cells(i, 11)
SDeliveryDate = detailsSheet.Cells(i, 14)
SWhsAddress = detailsSheet.Cells(i, 18)
SClientAddress = detailsSheet.Cells(i, 15)
SClientTownZip = detailsSheet.Cells(i, 16)


'Generating the output
'reportSheet.Cells(19, 1).Value = SPile
'reportSheet.Cells(17, 1).Value = SClient
reportSheet.Cells(17, 1).Value = SCommodity
reportSheet.Cells(17, 5).Value = SOption
reportSheet.Cells(17, 4).Value = SIncoterm
'reportSheet.Cells(1, 1).Value = SWhs
reportSheet.Cells(17, 2).Value = SDeliveryCity
reportSheet.Cells(21, 2).Value = SPriceMT
reportSheet.Cells(17, 6).Value = SPO
reportSheet.Cells(17, 3).Value = SDeliveryDate
'reportSheet.Cells(5, 1).Value = SWhsAddress
reportSheet.Cells(21, 1).Value = SQtyMT
reportSheet.Cells(10, 6).Value = SClient
reportSheet.Cells(11, 6).Value = SClientAddress
reportSheet.Cells(12, 6).Value = SClientTownZip


'Save the PDF file
Worksheets("Contract Form").Range("A1:G28").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\JonathanGerafi\Desktop\" & SPO & ".PDF", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

With ActiveSheet.PageSetup
.Zoom = False
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Next i

End Sub

 

[Nathanoj]

Hi Holger, sorry for my late response.
Thank you for the detailed solution but as you correctly pointed out it took a little bit of a wrong turn and might have been to advanced for me. But I ultimately changed the setup by adding parameters to filter the Pile number and from there I used a simple macro to copy the specific range range needed...