Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
some final help
Hi this almost works - my only problem is that i don't know how to selec 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 fi this ActiveCell.EntireRow.Paste Sheets("Sheet1").Select ActiveCell.Offset(1, 0).Select Loop Until ActiveCell.Offset(-1, 0) = "" End Sub thank -- rbekka3 ----------------------------------------------------------------------- rbekka33's Profile: http://www.excelforum.com/member.php...fo&userid=1403 View this thread: http://www.excelforum.com/showthread.php?threadid=26274 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
some final help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
final return! | Excel Worksheet Functions | |||
Final day of month | Excel Worksheet Functions | |||
My Final #DIV/0! that I'd like to say Goodbye to! | Excel Worksheet Functions | |||
Help Finding the Final Row | Excel Programming | |||
Need final code tweak | Excel Programming |