vendredi 5 avril 2019

How to add exceptions in the creation of pdf files according to an excel list using macros

Hi I downloaded an excel file with macros that generates pdf files according to a list. There are 2 sheets and the pdf are generated from the sheet called "WEST" to generate them it uses an Autofilter function in column D so it generates a pdf for each unique value specified in the list from the sheet called "PRACTICE".

Here is the link to the file http://nhsexcel.com/filtered-list-to-pdf/

The thing is that I want to add exceptions to the code, for example I don´t want to generate pdf´s of the rows in the sheet "WEST", that contain in column i values less than 10.

I tried to add an autofilter with that criteria but the code keeps saying that it´s not a valid metod.

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

I would like to just generate the pdf files of all the rows which value in column I is greater than ten, but no matter what I have tried it keeps either generating all the pdfs or not generating any at all.

Aucun commentaire:

Enregistrer un commentaire