For Each repeating on same data

[Luke777]

Hi all,

I have a workbook from which I'm trying to copy the entire row IF the date matches the date I'm working with. Below is a section of my code - certainly not the best way to do this, but I've been trying to iterate and teach myself what and how it works and improve it as I go along. However, this particular bit of code only copies the same row (there's multiple rows with the matching date) over and over again instead of moving through the column (column A is where the dates are).

With wb2
For Each Cell In ws2.Range("A1:A10000")
If Cell.Value = activeDate Then Cell.EntireRow.Copy Destination:=ws3.Range("A:A")
Next
End With

Any help here would be appreciated

Thanks all!

 

[BSALV]

i didn't test it, but something like this.

If Cell.Value = activeDate Then Cell.EntireRow.Copy Destination:=ws3.Range("A" & rows.count).end(xlup).offset(1)

 

[Michael M]

This needs to be defined as a cell not a column
Maybe

Dim lr As Long
With wb2
lr = ws3.Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each Cell In ws2.Range("A1:A10000")
If Cell.Value = activeDate Then 
Cell.EntireRow.Copy Destination:=ws3.Range("A" & lr)
lr = lr + 1
end if
Next
End With

 

[Luke777]

i didn't test it, but something like this.

If Cell.Value = activeDate Then Cell.EntireRow.Copy Destination:=ws3.Range("A" & rows.count).end(xlup).offset(1)

That worked perfectly thank you - seems confusing that changing the destination part affects the function of the code in that way

 

[Luke777]

i didn't test it, but something like this.

If Cell.Value = activeDate Then Cell.EntireRow.Copy Destination:=ws3.Range("A" & rows.count).end(xlup).offset(1)

Not sure if we're meant to ask follow up questions or if I'd be better off starting a new thread, but I was wondering if there was an easy method that replaces Cell.EntireRow.copy with specific columns from that row? e.g, instead of all of row, just A, D,E and P from that row.

Thanks!