VBA Select Multiple Rows Issue

[AllAboutExcel]

Hi!

Looking for some assistance with a VBA code. Data will come into the report daily and I want the macro to store the data.

The purpose is that it will select the day previous to yesterday & copy and paste the values. I've got it working - however I've now realised I need to do it for three days previous (to include weekends).

So I want the code to say... 'Find todays date, offset to the row above & select that row plus 2 rows higher.

Here is my current code -


For Each cell In ActiveSheet.Range("A:A")
If cell.Value = [Today()] Then
cell.Offset(-1).EntireRow.Select
End If
Next

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues



Thanks in advance

 

[Fluff]

Hi & welcome to the board.
If you are trying to change those rows into values try

Sub chk()
   Dim fnd As Range
   Set fnd = Range("A:A").Find(Date, , , , , , , , False)
   If Not fnd Is Nothing Then
      With fnd.Offset(-3).Resize(3).EntireRow
         .Value = .Value
      End With
   End If
End Sub

 

[AllAboutExcel]

Worked perfectly! Thank you so much, I never thought about doing it this way!

 

[Fluff]

Glad to help & thanks for the feedback