check for dates in a date and move to new tab ,

[david gabra]

Hi
For each travel lD (very important) (column A), is it possible to check for dates ((column D and E) to see if they fall within a date range (column B and C) and if so, move all occurrences to a new tab to show all occurences.
Excel attached showing expected results.
e.g. Travel ID 11 - there were 2 occurrences from 4 lines, so to move those 2 occasstion to and Travel ID 15 - one time.
thanks

 

[PCosta87]

Hi David,

Try this solution please and see if it works for you... i used column F to place an "if" formula to return 1 when the criteria for the dates is met, then used that in the code below to copy the relevant entries to the other sheet:

Sub move()

    Dim cel As Range
    Dim lastrow As Long
       
    For Each cel In Sheets(1).Columns("F").Cells
        If cel.Value = 1 Then
            cel.EntireRow.Copy Sheets(2).Range("A" & Sheets(2).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1)
        End If
    Next cel
   
End Sub

It may not be very elegant and can definitely use some work but i believe does the trick

Please see attached

Let me know if it helped

 

[p45cal]

Added column Within to original table, created straightforward pivot table from that table on to move sheet.

 

[david gabra]

Thanks - your logic is sound.

 

[david gabra]

Hi David,

Try this solution please and see if it works for you... i used column F to place an "if" formula to return 1 when the criteria for the dates is met, then used that in the code below to copy the relevant entries to the other sheet:

Sub move()

    Dim cel As Range
    Dim lastrow As Long
      
    For Each cel In Sheets(1).Columns("F").Cells
        If cel.Value = 1 Then
            cel.EntireRow.Copy Sheets(2).Range("A" & Sheets(2).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1)
        End If
    Next cel
  
End Sub

It may not be very elegant and can definitely use some work but i believe does the trick

Please see attached

Let me know if it helped

- thank you - I see that great. Macros are very useful - thanks once again.