Hi I really need expert help in this project. I already made a part of my project working. I have an excel file with client data and tracking of missed service. We send report per client, so I put a field with a drop down list with all our client name plus a field for date. We enter all our data in a table with header. When we want to pull out a report we enter the client name and desired date for the report and it create a PDF. Until there everything is perfect. We want Excel to give us the choice to save the file or to open Outlook with a temporary generated report PDF (without saving).
On another hand 2 of our client request us to used their specific report template (Excel Template file) and sent it in an excel file following their template ... So I want to put a condition in the PDF Macro , that if client A is specified it will copy content from some specific column, open the template file, copy info in desired row than show us a pop up to save excel file or send it by email with outlook. Same thing for client B.
Ex : Client A is selected, Macro will filter result than copy text from Range A13,C13,E13,F13 to Client template File one Line under Range (A13 --> D6 , C13 --> E6, E13 --> B6 ... )
Here is a part of my code, Let me know if you need more details or explanation.
Thanks for your help,
Sub PDFActiveSheet()
Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo errHandler
Set ws = ActiveSheet
myFile = Application.GetSaveAsFilename _
(InitialFileName:="REPORT" & " - " & ActiveSheet.Range("I11") & " " & VBA.Format(Now(), "yyyy-mm-dd"), _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")
If myFile = "Faux" Then Exit Sub
With Range("A13:P13").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=Range("I11").Value
Selection.AutoFilter Field:=1, Criteria1:="=" & Range("I10").Value
End With
If myFile <> "False" Then
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
ActiveSheet.ShowAllData
End If
exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler
End Sub
Bookmarks