Modify Macro to Run on Pages After Cell >=200

[everclearacg]

Hi Everyone,

I hope someone has a solution for me on this. I have this macro that will combine a range of cells from daily worksheets onto a worksheet called "Mileage". My finance department has some quirky rules though. 1st, anything that is over 200 miles for a day has to be submitted separately and and days can't overlap.

Example:

Day 1 25 miles - Submission 1
Day 2 30 miles - Submission 1
Day 3 200 miles - SUBMIT SEPERATELY on Submission 2
Day 4 15 miles - Submission 3
Day 5 18 miles - Submission 3

What the macro below will do is cycle through the daily worksheets and group the days where mileage is less than 200 miles and when it finds one that is 200 miles it stops and creates a PDF that doesn't include the 200+ day. I need a macro to do one of 2 things if possible. THANK YOU SO MUCH FOR EVEN JUST LOOKING AT THIS PROBLEM!

Option 1.
A macro that will group worksheets AFTER the one with 200+ miles on it. So like the example above would combine Day 4, Day 5, Day 6 and Day 7 for it's own separate PDF. I would like to not add an additional worksheet but if I have to I can, like Mileage2 or something. Also, a 200+ mile day could fall on any Day# sheet.

Option 2.
A macro that will combine everything but will create 3 separate PDFs. Based on the example above, a PDF for Submission 1, a PDF for Submission 2, and a PDF for Submission 3.

Sub CombineMileagev2()


'
' Macro6 Macro
'

'
'LOOP CODE
Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning

   Sheets("Mileage").Select
    Range("A2").Select

For Each ws In ThisWorkbook.Worksheets
    ws.Activate


'LOOP CODE
        Select Case LCase(ws.Name)
        Case "instruction", "prev wkbk", "mileage"
             'do nothing
        Case Else
If ActiveSheet.Range("B1").Value >= 200 Then GoTo eh
    Call HideforEmail
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("Mileage").Select
    ActiveSheet.Paste
    Selection.End(xlToLeft).Select
    Selection.End(xlDown).Select
    Selection.Offset(2, 0).Select
End Select
'LOOP CODE
Next

eh:
    Sheets("Mileage").Select
    
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-7]="""","""",IF(ISNUMBER(RC[-2]),"""",IF(RC[-6]&R[1]C[-7]=""Miles1"","""",IF(RC[-6]<0.01,""HIDE"",IF(R[1]C[-6]&R[2]C[-7]<>""Miles1"",""HIDE"","""")))))"
    Range("H2").Select
    Selection.Copy
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.Paste
    ActiveSheet.Range("$A$1:$H$1412").AutoFilter Field:=8, Criteria1:="="
    Range("A3").Select
    Application.CutCopyMode = False
    
'Delete Empty Rows
    Range("A65536").Select
    Selection.End(xlUp).Select
    Selection.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    Range("A1").Select
    
    ActiveSheet.Range("$A$1:$H$1412").AutoFilter Field:=3, Criteria1:=">1/1/2017" _
        , Operator:=xlAnd
    
    
    Range("C1").Select
    Selection.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveSheet.Range("$A$1:$H$1412").AutoFilter Field:=3
    
    ActiveSheet.Range("$A$1:$H$1412").AutoFilter Field:=3, Criteria1:="mm/dd/yy"
    Range("C1").Select
    Selection.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveSheet.Range("$A$1:$H$1412").AutoFilter Field:=3
    
    Range("A1").Select

   Call RDB_Worksheet_Or_Worksheets_To_PDF
End Sub

Lastly, I have the macro calling on a different macro and here is the info if you find it useful.

Sub HideforEmail()
'
' HideforEmail Macro
'

'

Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).Row

    Rows("2:2").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("A3").Select
    Range("C:C,G:G,N:N,O:O,Q:Q,T:Y,AA:AC").Select
    Range("AA1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Range("A2:$AC" & LastRow).AutoFilter Field:=1, Criteria1:="<>"
    Range("A2").Select
End Sub