[gerry125]

I'm trying to write a macro in Excel (Vista) to look in column "H" of one sheet and find all instances of an entry "20" and then select that row of data in columns a:g and copy it below the last line of data in a second sheet.

Should be very easy - but I'm getting nowhere. So for any help I will be very grateful.

Gerry125

[turbodante]

Welcome to the forum Gerry125.

try this...

Sub Macro9()
'
Dim X As Long, DRw As Long
Dim DSheet As Worksheet, PSheet As Worksheet
Set DSheet = Sheets("Sheet1")
Set PSheet = Sheets("Sheet2")
DRw = DSheet.Cells(Rows.Count, "H").End(xlUp).Row
For X = DRw To 1 Step -1
If DSheet.Cells(X, "H") = "20" Then
DSheet.Range("A" & X & ":G" & X).Copy PSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Next
End Sub

HTH. Let us know if you need it tweeked.

[gerry125]

Thanks very much - it very nearly works properly now. I've tweeked to fit my workbook so the code reads

Sub Macro9()
'
Dim X As Long, DRw As Long
Dim DSheet As Worksheet, PSheet As Worksheet
Set DSheet = Sheets("Future Transactions")
Set PSheet = Sheets("Current")
DRw = DSheet.Cells(Rows.Count, "H").End(xlUp).Row
For X = DRw To 1 Step -1
If DSheet.Cells(X, "H") = "20" Then
DSheet.Range("A" & X & ":E" & X).Copy PSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1)
End If
Next
End Sub

Only problem is that there can be more than one row that is Flagged as 20 in column "H" and i want them all copied in.
Start position in "Current is Column B so I changed the paste location.

Rgds

Gerry

[turbodante]

Only problem is that there can be more than one row that is Flagged as 20 in column "H" and i want them all copied in.

The code should work for ALL instances of "20" in column H of yiour "Future transactions" sheet. So i don;t understand what you mean by this, can you reframe the problem?

[gerry125]

turbodante

Thanks for the continued interest..

It was me mucking about with your code. I changed the wrong part of the line -

DSheet.Range("A" & X & ":E" & X).Copy PSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).

Its all fixed now. Wonderful and thanks very much.

Gerry

[turbodante]

Great stuff. Please mark the top of the thread 'Solved' thanks. See you around.