Basic VBA to copy a range if today()

[lidcimc]

I need to copy a range (col B:col F) if date in col A is today's date. Then I need to paste that data into col B:col F on another worksheet if col A is today's date.

My goal is maintain a history file in a hidden sheet. Thoughts?

 

[SkipVought]

Hi,

What code do you have so far?

Where are you stuck?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lidcimc]

This is what I have so far - I get a "400" error after the copy.


Sub CopyRow()
Dim Rng As Range
Dim i As Range

Set Rng = Range("A15", Range("A" & Rows.Count).End(xlUp))
For Each i In Rng
If i = Date Then
i.EntireRow.Copy
Sheets("History Stats").Range("A3" & Rows.Count).End(xlUp)(2). _
EntireRow.PasteSpecial
End If
Next i
End Sub

 

[SkipVought]

try this...

Sub CopyRow()
   Dim Rng As Range
   Dim i As Range
   
   Set Rng = Range("A15", Range("A" & Rows.Count).End(xlUp))
   For Each i In Rng
       If i = Date Then
           i.EntireRow.Copy
           With Sheets("History Stats")
                Range(.[A3], .[A3].End(xlDown)).Offset(1).PasteSpecial xlPasteAll
           End With
       End If
   Next i
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lidcimc]

Getting closer - 1 row copied but the result is 5 duplicate rows pasted - not just the one.

Is there something in this line of code that can be tweaked?

Range(.[A3], .[A3].End(xlDown)).Offset(1).PasteSpecial xlPasteAll

 

[SkipVought]

  .[A3].End(xlDown).Offset(1).PasteSpecial xlPasteAll

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}