Hi,

Pls help me with this. I have two workbook.

Workbook 1 - with columns A B C D

I need a macro that will do the following:

1) From workbook 1 - If the cell D1 = "x" , then I need to copy cells
A1& B1 to workbook 2.

2) I need the macro to go down the page in workbook 1 to identify the
criteria (I don't know what row it will end).

3) When pasting to workbook 2, i need the macro to identify a blank
row before pasting (there could be data there previuosly that i do not
want to delete).

Thankyou so much
KH

Adjust workbook name and criteria to suit
try this

Sub t()
Dim rng As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim targetrng As Range

Set awf = Application.WorksheetFunction
Set rng = Range(Cells(1, 4), Cells(1, 4).End(xlDown))
firstrow = awf.Match("x", rng, 0)
Rows("1:" & firstrow - 1).Delete
Range("A1").CurrentRegion.AutoFilter Field:=4, Criteria1:="x"
Cells(1, 1).CurrentRegion.Copy

Set wb = Workbooks.Open("workbook2.xls")
Set ws = wb.Sheets(1)
Set targetrng = ws.Range("a65536").End(xlUp)(2)
targetrng.PasteSpecial

End Sub

"missk" wrote:

Hi,

Pls help me with this. I have two workbook.

Workbook 1 - with columns A B C D

I need a macro that will do the following:

1) From workbook 1 - If the cell D1 = "x" , then I need to copy cells
A1& B1 to workbook 2.

2) I need the macro to go down the page in workbook 1 to identify the
criteria (I don't know what row it will end).

3) When pasting to workbook 2, i need the macro to identify a blank
row before pasting (there could be data there previuosly that i do not
want to delete).

Thankyou so much
KH

Thank you so much. It worked.