+ Reply to Thread
Results 1 to 2 of 2

Macro to AutoFilter create PDF/Mail and copy selected data to Template Excel file

Hybrid View

  1. #1
    Registered User
    Join Date
    06-04-2014
    Posts
    2

    Macro to AutoFilter create PDF/Mail and copy selected data to Template Excel file

    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
    Last edited by iancoupal; 06-07-2014 at 05:50 PM.

  2. #2
    Registered User
    Join Date
    06-04-2014
    Posts
    2

    Re: Macro to AutoFilter create PDF/Mail and copy selected data to Template Excel file

    Excel.jpg Here is a screen shot of the actual report

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Get Subject of a selected E-mail and Copy the msg file
    By plans in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2014, 10:24 PM
  2. Replies: 11
    Last Post: 05-12-2013, 11:30 AM
  3. Macro to create an e-mail using an Outlook Template and add an attachment
    By hsarro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2012, 12:25 PM
  4. Create a macro to send mail from selected profile
    By vivek_work in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2012, 04:15 PM
  5. Replies: 1
    Last Post: 07-16-2009, 09:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1