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
Intersect
. If the table on Sheet5 always starts at cell A1, you can simplify it to:Set rngData = Sheet5.UsedRange.Columns("H")
.Microsoft documentation:
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