Find next blank row
Hi i have been using this macro to cut and paste data from one sheet to another when i run the macro the first time it works fine but then i run the macro the second time the macro over rides my data how do i make it so that data is pasted into the next available blank row in sheet 2 Code: -------------------- Sub SearchForString() Dim LSearchRow As Integer Dim LCopyToRow As Integer On Error GoTo Err_Execute 'Start search in row 4 LSearchRow = 2 'Start copying data to row 2 in Sheet2 (row counter variable) LCutToRow = 2 While Len(Range("F" & CStr(LSearchRow)).Value) 0 If Range("F" & CStr(LSearchRow)).Value = "RG Complete" Then Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Cut Sheets("Sheet2").Select Rows(CStr(LCutToRow) & ":" & CStr(LCutToRow)).Select ActiveSheet.Paste LCutToRow = LCutToRow + 1 'Go back to Sheet1 to continue searching Sheets("Sheet1").Select End If LSearchRow = LSearchRow + 1 Wend 'Position on cell A3 Application.CutCopyMode = False Application.ScreenUpdating = False Range("F2").Select MsgBox "All RG Complete data has been copied." Call macro DeleteBlankRows Exit Sub Err_Execute: MsgBox "An error occurred." End Sub -------------------- -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
Find next blank row
Anyone? It shouldn't be that hard should it? -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
Find next blank row
Hello Vlad999, Change... 'Start copying data to row 2 in Sheet2 (row counter variable) LCutToRow = 2 To this... With Sheets("Sheet2") LCutToRow = .Cells(.Rows.Count, "F").Row + 1 End With This will return the next blank row in column "F" on Sheet2. Sincerely. Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
Find next blank row
Thank you :) -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=556718 |
All times are GMT +1. The time now is 12:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com