Correction, I am that someone. Even if that someone used trial and error for hours to figure it out.
Don't know why anyone else would ever need/use this overly complicated code but here's it is.
Sub PrintPDF()
Dim FileName As String
Dim trSh As Worksheet
Dim trRegSh As Worksheet
Dim Docsht As Worksheet
Dim destRow As Long
Dim testRow As Long
Dim Nametest As Worksheet
Dim n As Integer, m As Integer
Dim r As Integer, i As Integer
Dim myRecipients As String
Dim FPath As String
Dim NameEase As String
FPath = "C:\Users\rthomson\Desktop\"
With ThisWorkbook
Set trSh = .Sheets("Transmittal Sheet")
Set trRegSh = .Sheets("Transmittal Register")
End With
'save as pdf
FileName = trSh.Cells(12, "R")
trSh.ExportAsFixedFormat xlTypePDF, FileName:= _
FPath & FileName & ".pdf"
'move data from transmittal sheet to transmittal register
destRow = trRegSh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 12 To 15
'for each recipient
If Trim(trSh.Cells(i, "c")) = "" Then
Exit For
Else
If i > 12 Then
myRecipients = myRecipients & "; "
End If
myRecipients = myRecipients & trSh.Cells(i, "c")
End If
Next i
For r = 26 To 33
'for each document
If Trim(trSh.Cells(r, "h")) = "" Then
Exit For
End If
NameEase = trSh.Cells(r, "h")
Set Nametest = Sheets(NameEase)
testRow = Nametest.Range("B30").End(xlUp).Row
destRow = destRow + 1
testRow = testRow + 1
trRegSh.Cells(destRow, "b") = trSh.Cells(r, "f")
trRegSh.Cells(destRow, "c") = trSh.Cells(r, "h")
trRegSh.Cells(destRow, "d") = trSh.Cells(r, "j")
trRegSh.Cells(destRow, "e") = "DCC"
trRegSh.Cells(destRow, "f") = myRecipients
trRegSh.Cells(destRow, "g") = trSh.Cells(39, "R")
trRegSh.Cells(destRow, "h") = Date
trRegSh.Cells(destRow, "i") = trSh.Range("r40")
trRegSh.Hyperlinks.Add Anchor:=trRegSh.Cells(destRow, "a"), _
Address:=FPath & FileName & ".pdf", _
ScreenTip:="Click to open Transmittal", _
TextToDisplay:=FileName
Nametest.Cells(testRow, "B") = trSh.Cells(12, "R")
Nametest.Cells(testRow, "E") = trSh.Cells(r, "f")
Nametest.Cells(testRow, "F") = myRecipients
Nametest.Cells(testRow, "K") = trSh.Cells(39, "R")
Nametest.Cells(testRow, "N") = Date
Next r
Set trSh = Nothing
Set trRegSh = Nothing
End Sub
Now if only I could give myself rep.
Bookmarks