VBA - I'm getting in a muddle.........

[MOB]

I've got some code that basically does this;

- changes the value of cell J1 using a data validation list, pulls through data using lookups etc
- if "print" or "print only" is in cell A5, it prints a copy of a payslip
- otherwise it emails the payslip
- loops through the list til the end

This is what I need it to do;

I want it to print the payslips if cell A5 says "print" or "print only" and also cell E5 contains "BACS" or "PAYE".

I want it to email the payslips if cell A5 doesn't say "print" or "print only" and also cell E5 contains "BACS" or "PAYE".

Otherwise it does nothing.

I think I'm 99% there but I cannot get this to work!

TIA

Sub LoopthroughDataValidation()
    Dim rCell As Range
    Dim Data As Range
    Dim a As Range
    Set a = Range("J1")
    
    If MsgBox("Are you sure you want to print/email ALL payslips?", vbYesNo) = vbYes Then
    
    For Each rCell In Range("Performers")
    
    a.Value = rCell.Value
    
    If Sheets("Pay Advice").Range("A5") = "Print" Then
    Range("Payslip").PrintOut
    ElseIf Sheets("Pay Advice").Range("A5") = "Print only" Then
    Range("Payslip").PrintOut
    Else
    
  Dim FileName As String

    If ActiveWindow.SelectedSheets.Count > 1 Then
        MsgBox "There is more then one sheet selected," & vbNewLine & _
               "be aware that every selected sheet will be published"
    End If

    'Call the function with the correct arguments
    'Tip: You can also use Sheets("Sheet3") instead of ActiveSheet in the code(sheet not have to be active then)
     'FileName = RDB_Create_PDF(ActiveSheet, "", True, False)
     FileName = RDB_Create_PDF(ActiveSheet, "C:\Payslip.pdf", True, False)

    'For a fixed file name and overwrite it each time you run the macro use
    'RDB_Create_PDF(ActiveSheet, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)

    If FileName <> "" Then
        RDB_Mail_PDF_Outlook FileName, Sheets("Pay Advice").Range("A5"), "PRIVATE & CONFIDENTIAL", _
                             "Please find attached your Payslip for the work done in the previous month." _
                           & vbNewLine & vbNewLine & "Regards", False
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to Save the file in arg 2 is not correct" & vbNewLine & _
               "You didn't want to overwrite the existing PDF if it exist"
    End If
    End If
       
    Next rCell
    
    Else
    End If
End Sub

 

[MOB]

Bump