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.

Unfortunately 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.