Posts by KjBox

    You don't need line 6 of the code either. My amendment to your original code merely added an extra If..Then..End If statement to account for the current month being January. All you need to do is add your code for getting the previous month's data after each "Then".

    Possibly something along these lines

    Code
    1. If Month(Date) > 1 Then
    2. If Year(x(i, DtCol)) = Year(Date) And Month(x(i, DtCol)) = Month(Date) - 1 And x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then
    3. 'Your code here
    4. End If
    5. ElseIf Year(x(i, DtCol)) = Year(Date) - 1 And Month(x(i, DtCol)) = 12 And x(i, 3) = ContAreas(ii) And x(i, 7) = Category And x(i, 8) = InspType Then
    6. 'Your code here
    7. End If
    8. End If

    Try this.


    My code will generate a string of Email recipients who meet the combo box criteria. That string can then be parsed to your emailing code to be used in the "To" bit of the code (.To sTo)

    Try the attached.


    On Sheet 1 the cell linked to the List Box is E2, The list of weekdays starting in J2 is used to populate the list box (you can move it anywhere, even to a hidden sheet, it is a named range "Days". That named range is also used in the code.


    The code assigned to the ListBox is

    Alternatively try the attached, click the button on Sheet 1.


    Code assigned to the button:

    Like this

    Try this

    Try changing

    Code
    1. newFullPath = WBPath & "\" & WBName
    2. ThisWorkbook.SaveAs fileName:=CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm"

    to

    Code
    1. newFullPath = WBPath & "\" & WBName & "\"
    2. ThisWorkbook.SaveAs fileName:=newFullPath & CustomerName & "_" & PricingAnalysis & "_" & Format(Date, "MM.DD.YYYY") & ".xlsm"

    Try this alternative approach, file attached


    Code assigned to the button

    Files

    CurrentRegion stops at an empty row, so the "Record Only" would not be included. Try