excelvba

Transfer rows to another sheet based on todays date in a set column


Sheet5 has two columns of dates, G and H, but I only want to select rows based on todays date in column H, not on column G and column H, to copy and transfer to sheet8. This code is fine but is selecting from both the two columns. How do I tell it to only select from the one please. Spent all day on this but I am very much a novice.

Sub dueOutToday()
    Sheet8.Range("A2:U50").Clear
    Dim todaysDate As Date
    Dim rngData As Range
    Dim rngRow As Range
    Dim rngCell As Range
    Dim counter As Integer

    todaysDate = Date
    counter = 1

    Set rngData = Sheet5.UsedRange

    For Each rngRow In rngData.Rows
        For Each rngCell In rngRow.Cells
            If rngCell.Value = todaysDate Then
                rngRow.Copy Sheet8.Rows(counter).Columns(1)
                counter = counter + 1
                Exit For
            End If
        Next
    Next

End Sub

Tried

 Set rngRow = Range("H", col)

tried

 If Range("H",col).Value = todaysDate Then

Solution

  • Microsoft documentation:

    Range.EntireRow property (Excel)

    Application.Intersect method (Excel)

    Sub dueOutToday()
        Sheet8.Range("A2:U50").Clear
        Dim todaysDate As Double
        Dim rngData As Range
        Dim rngCell As Range
        Dim counter As Long
        
        todaysDate = CDbl(Date)
        counter = 1
        With Sheet5
            Set rngData = Application.Intersect(.UsedRange, .Columns("H"))
        End If
        If rngData Is Nothing Then Exit Sub
        For Each rngCell In rngData.Cells
            If CDbl(rngCell.Value) = todaysDate Then
                rngCell.EntireRow.Copy Sheet8.Cells(counter, 1)
                counter = counter + 1
            End If
        Next
        
    End Sub