Macros to export filtered PDF from log

[chullan88]

Hi,

I have attached a small version of the logs we use.It has hundreds of entries with revisions.Every week I have to filter this list and submit to management according to some criteria.



When I use Filter option, I have problem because Im not getting heading and title rows in Rows 2 and 4 to appear


Im trying to automate this process.
So im trying to get 3 buttons with assigned macros for printing and saving the PDF version to same folder for :

1. Full Log Entries
2. Filtered with items "LATEST" only in Column I
3. Filtered with items "LATEST" and "LATE" in I and J respectively

In all the PDFs,the heading and title row should also be printed.



Thanks in advance

[Sintek]

Hi chullan88

Let me see if I understand correctly.....Do you want the following result....
1. A Full version to be saved to pdf in folder and printed...
2. a version with "LATEST" Filtered to be saved and printed
2. a version with 2 filters "LATEST" & "LATE" Filtered to be saved and printed

[chullan88]

Hi sintek,

Yes thats what Im trying to achieve with three buttons assigned macros
All three versions will be PDF in same folder
The title row and heading should be there in all three

[Sintek]

Have a look at attached... I have put it all in one button as it is not necessary for 3 buttons.

Option Explicit

Sub PDF_Filter()
Dim lRow As Long, i As Long
Dim ws As Worksheet
Dim CritArr As Variant
Application.ScreenUpdating = False
Set ws = Sheets("Civil log")
lRow = ws.Cells(Rows.Count, "J").End(xlUp).Row
CritArr = Array("LATEST", "LATE")
With ws
    '..................First Button Function
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="Full Version" _
                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
    .PrintPreview 'change to .PrintOut
    For i = LBound(CritArr) To UBound(CritArr)
        .AutoFilterMode = False
        With .Range("A4:J" & lRow)
            If i = 0 Then
                '..................Second Button Function
                .AutoFilter Field:=9, Criteria1:=CritArr(i)
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=CritArr(i) & " Filtered" _
                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
            Else
                '..................Third Button Function
                .AutoFilter Field:=9, Criteria1:=CritArr(i - 1)
                .AutoFilter Field:=10, Criteria1:=CritArr(i)
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=CritArr(i - 1) & "&" & CritArr(i) & " Filtered" _
                    , Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If
        End With
        '..................Second & Third Button Function
        .PrintPreview 'change to .PrintOut
    Next i
    .AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub

[chullan88]

Many thanks Sintek..

It'll take some time to understand this code..

Will get back to you after I get a hold..