≡ Menu

Macro to convert filtered list to multiple pdf

Macro

We generate reports that hold details for multiple practices all in one sheet, which is nice and convenient for me as I can run a pivot table off it and analyse any number of practices and services as I see fit.

filtered list to pdfUnfortunately this report also needs to go out to the individual practices and without a sharing agreement in place, the reports need to be cut into smaller, single practice chunks, so that practices can see their own data and not that of their neighbours.

If you are going to do that task manually, by filtering and then saving each as an individual PDF, you will very quickly come to hate the tedium of your job and quit.

This is where a macro comes in handy and saves your sanity.

I have a list of all my unique practice codes in the tab PRACTICE, this could be a pivot table or just a fixed list. I want my macro to work through this list, filtering the main report in tab WEST to show each individual practice and saving a PDF copy as it goes along.

The macro below does this.

Sub PracticeToPDF()
'Prepared by Dr Moxie

    Dim ws As Worksheet
    Dim ws_unique As Worksheet
    Dim DataRange As Range
    Dim iLastRow As Long
    Dim iLastRow_unique As Long
    Dim UniqueRng As Range
    Dim Cell As Range
    Dim LastRow As Long
    Dim LastColumn As Long
 
    Application.ScreenUpdating = False
 
    'Note that the macro will save the pdf files in this active directory so you should save in an appropriate folder
    DirectoryLocation = ActiveWorkbook.Path
 
    Set ws = Worksheets("WEST") 'Amend to reflect the sheet you wish to work with
    Set ws_unique = Worksheets("PRACTICE") 'Amend to reflect the sheet you wish to work with
    
    'Find the last row in each worksheet
    iLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    iLastRow_unique = ws_unique.Cells(Rows.Count, "A").End(xlUp).Row
 
 
    With ws
        'I've set my range to reflect my headers which are fixed for this report
        Set DataRange = ws.Range("$A$8:$L$" & iLastRow)
 
        'autofilter field is 4 as I want to print based on the practice value in column D
        DataRange.AutoFilter Field:=4
 
        Set UniqueRng = ws_unique.Range("A4:A" & iLastRow_unique)
        For Each Cell In UniqueRng
            DataRange.AutoFilter Field:=4, Criteria1:=Cell
 
        Name = DirectoryLocation & "\" & Cell.Value & " Practice Report" & ".pdf"
 
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
 
        Next Cell
 
    End With
    With ws
         .Protect Userinterfaceonly:=True, _
         DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingColumns:=True, AllowFormattingRows:=True
         .EnableOutlining = True
         .EnableAutoFilter = True
         If .FilterMode Then
            .ShowAllData
         End If
     End With
    Application.ScreenUpdating = True
 
End Sub

Some points to bear in mind:

  1. All the PDF copies will be stored in your active directory so it is a good idea to save the original file in a suitable folder – perhaps relating to the month of interest
  2. The PDF will be saved according to the print settings in place so you should make sure this is set up correctly before you press the macro button. In this example spreadsheet my macro button shows on each PDF, if you don’t want that, and why would you, you could set the print range to exclude it.

Here’s the example spreadsheet for you to experiment with creating multiple pdf documents from a filtered list Filtered_List_To_PDF.

Doctor Moxie

Written by -

NHS Accountant with geeky tendencies - serial blogger on subjects varying from Excel, Raspberry Pi, productivity, allotment gardening and running. The NHSExcel blog is reserved for Excel topics.

Comments on this entry are closed.

  • Mattias Hallberg

    HI!
    When I try using the exampel spreadsheet attached I get the following error message: Run-time error ‘1004’:

    Method ‘AutoFilter’ of object ‘Range’ failed

    What could be wrong?

    • Mattias Hallberg

      Could it be a problem that I use a mac book?

      • http://whatapalaver.co.uk Dr Moxie

        Possibly. The Mac version of excel is quite different and I haven’t tested it.
        I’ve just downloaded the example onto my PC at work and providing I unprotected the worksheet first, it runs without errors.

        Perhaps you could try recording a macro on your Mac and apply filters to see if the VBA terminology is different.

        • Vaibhav Shukla

          Hi Dr
          Please help or guide to resolve this error.
          When I try using the example spreadsheet attached I get the following error message: Run-time error ‘5’:

          Invalid Procedure call or argument

          ( ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name _
          , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
          :=True, OpenAfterPublish:=False)

          Is any add-in required ????