Hi rbekkR,

You can return the last populated cell in column A with an expression like:

Set LastCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)

Similarly, the following empty cell would be returned by:

Set NextCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2).

It is rarely necessary to select, so removing selections/activations and
dropping your Do ...Loop, your code could, perhaps, be simplified to
something like:

Sub CopyPaste2()
Dim RngSrc As Range
Dim RngDest As Range
Dim ac As Variant

ac = ActiveSheet.Range("H5").Value

Set RngSrc = Workbooks("source.xls").Sheets("Data"). _
Columns(1).Find(What:=ac)

If Not RngSrc Is Nothing Then
With Workbooks("Database.xls").Sheets("Data")
Set RngDest = .Cells(Rows.Count, "A").End(xlUp)(2)
End With

RngSrc.EntireRow.Copy RngDest
Else
'The search data not found!
End If
End Sub


Check this on a COPY of your workbook until you are happy that it correctly
reflects your intentions.

---
Regards,
Norman



"rbekka33" wrote in message
...

Hi

this almost works - my only problem is that i don't know how to select
the very last row and paste the data - any ideas?


Sub CopyPaste()

Range("h5").Activate
Set ac = ActiveCell
ac.Copy


Do
Windows("source.xls").Activate
Sheets("Data").Activate
Range("a1").Activate
Range("a2").Columns("A:A").EntireColumn.Select
Selection.Find(what:=ac).Select
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Windows("Database.xls").Activate
Sheets("Data").Select


' the next lines need to be dynamic - to find the last row in column a
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(7562, 0).Range("A1").Select


'this won't paste because the areas are different - not sure how to fix
this
ActiveCell.EntireRow.Paste

Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(-1, 0) = ""

End Sub

thanks


--
rbekka33
------------------------------------------------------------------------
rbekka33's Profile:
http://www.excelforum.com/member.php...o&userid=14033
View this thread: http://www.excelforum.com/showthread...hreadid=262740