Hi Everyone,
We are redesigning our "Purchase Order generator" to work from one worksheet instead on multiple worksheets. Currently, we have 90 worksheets feeding from a MasterPage.
The below code runs through each worksheet and creates a PDF and attaches it to an email ready to send.
I have redesigned a one page template using INDEX,MATCH, & VLOOKUPS, to replace all 90 woorksheets, but I'm now stuck on how to modify the above code to cycle through the template.
The Template uses the PO_Number to populate the sheet.
I have created a list on the sheet of all the PO_Numbers using the Advanced Filter unique values from the MasterPage... not sure if this is needed.
From the MasterPage, I can double click the PO_Number to populate the TEMPLATE
Can I use the "Generate PDFs" Double Click event to cycle the ActiveCell down the column running the "Email_PDF_PO_TEMPLATE()" macro until it reaches another "Generate PDFs" value or BLANK and then Exit Sub?
When the MasterPage is set to "Purchase Order Summary", the table is filtered to show only the PO_Numbers for each stage in the PO_Column.
For Example:
Generate PDFs
1.1.1
1.1.2
1.1.3
Generate PDFs
1.2.1
1.2.5
1.2.6
(Cell blank) - no Save Path set
1.3.1
1.3.3
The 'Save Path' is one cell to the right of the "Generate PDFs". The "Generate PDFs" will not display if there is no value in the 'Save Path'
Sorry for such a long post... I hope this makes sense.
Any help would be greatly appreciated.
We are redesigning our "Purchase Order generator" to work from one worksheet instead on multiple worksheets. Currently, we have 90 worksheets feeding from a MasterPage.
The below code runs through each worksheet and creates a PDF and attaches it to an email ready to send.
Code:
Sub Email_PDF_PO_TEMPLATE()
'Working only in 2007 and up
Dim sh As Worksheet
Dim TempFilePath As String
Dim TempFileName As String
Dim FileName As String
'Temporary path to save the PDF files
TempFilePath = Range("Save_Path").Text
'Loop through every worksheet
For Each sh In ThisWorkbook.Worksheets
FileName = ""
'Test C14 for a mail address
If sh.Range("C14").Value Like "?*@?*.?*" Then
'If there is a mail address in C14 create the file name and the PDF
TempFileName = TempFilePath & sh.Name & " " _
& sh.Range("H5").Value & ".pdf"
FileName = RDB_Create_PDF(sh, TempFileName, True, False)
'If publishing is OK create the mail
If FileName <> "" Then
RDB_Mail_PDF_Outlook FileName, sh.Range("C14").Value, "Purchase Order", _
"<font size=""2"" face=""Calibri"">" & "Please find attached the Purchase Order: " & sh.Name & " " & sh.Range("H5").Value, False
Else
MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & vbNewLine & _
"The path to Save the file is not correct" & vbNewLine & _
"You didn't want to overwrite the existing PDF if it exists"
End If
End If
Next sh
End SubThe Template uses the PO_Number to populate the sheet.
I have created a list on the sheet of all the PO_Numbers using the Advanced Filter unique values from the MasterPage... not sure if this is needed.
From the MasterPage, I can double click the PO_Number to populate the TEMPLATE
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("PO_Column")) Is Nothing Then
If Sheets("BOQ").Range("AY1") = "Purchase Order Summary" Then
If ActiveCell.Value = "Generate PDFs" Then
MsgBox "This will generate the PDFs once the code is linked"
Range("A1").Select
Exit Sub
ElseIf ActiveCell.Value = isblank Then
MsgBox "You must insert a 'Save Path' first"
ActiveCell.Offset(0, 2).Select
Exit Sub
End If
Sheets("PO TEMPLATE").Range("PO_Number").Value = ActiveCell.Value
Sheets("PO TEMPLATE").Activate
End If
End If
End SubCan I use the "Generate PDFs" Double Click event to cycle the ActiveCell down the column running the "Email_PDF_PO_TEMPLATE()" macro until it reaches another "Generate PDFs" value or BLANK and then Exit Sub?
When the MasterPage is set to "Purchase Order Summary", the table is filtered to show only the PO_Numbers for each stage in the PO_Column.
For Example:
Generate PDFs
1.1.1
1.1.2
1.1.3
Generate PDFs
1.2.1
1.2.5
1.2.6
(Cell blank) - no Save Path set
1.3.1
1.3.3
The 'Save Path' is one cell to the right of the "Generate PDFs". The "Generate PDFs" will not display if there is no value in the 'Save Path'
Sorry for such a long post... I hope this makes sense.
Any help would be greatly appreciated.
