Results 1 to 1 of 1
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Total,Set Range and Publish PDF

    Hi,

    This Codes are publishing the PDF files, I need 2 modifications which will very useful to me.
    1. To generate a total on Column E of filter.
    2. To set range column B to F and exclude Column A in publishing the PDF File in the destination folder.
    and PDF's fit to page.

    Code:
    Sub DesFilter()
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False
    'DECLARE AND SET VARIABLES
    Dim rng As Range
    Dim rng2 As Range
    Set CurrentWb = ActiveWorkbook
    LastRow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range(Cells(5, 1), Cells(LastRow1, 1))
    Set rng2 = Range("A4:F" & LastRow1)
    '-----------------------------------------------
    'GET LIST OF FILTER CRITERIA
    rng.Copy
    [j5].Select
    ActiveSheet.Paste
    Selection.RemoveDuplicates Columns:=1, Header:=xlNo
    LastRow6 = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row
    '-----------------------------------------------
    'CREATE DIRECTORIES< NEW PDF, AND COPY FILTERED DATA
    For i = 5 To LastRow6
    [c2] = Cells(i, 10).Value
    wbName = Cells(i, 10).Value
    rng2.Select
    Selection.AutoFilter
    rng2.AutoFilter Field:=1, Criteria1:=Cells(i, 10).Value
    Path = "D:\Rentreceipt\"
    ActiveSheet.Range("B:F").ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & wbName & ".pdf", _
        IncludeDocProperties:=False, IgnorePrintAreas:=False
    Selection.AutoFilter
    [a1].Select
    Next i
    Range(Cells(5, 10), Cells(LastRow6, 10)).ClearContents
    Exit Sub
    ErrorHandler:
    MsgBox "The folder D:\Rentreceipt\" & wbName & "\ does not exist.  Check the path and try again."
    Range(Cells(5, 10), Cells(LastRow6, 10)).ClearContents
    Selection.AutoFilter
    [c2] = ""
    [a1].Select
    End Sub
    Thanks in advance.
    Last edited by zmagic; 2016-03-26 at 18:09.

Posting Permissions

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