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