Pasting rows below the last row with data?
 

I've got a macro which Tom and Don kindly helped me with - it searches
for 'n' in Column F and if it find it copies that whole row into a
sheet called 'Diary'. Only problem is I need it to copy it to the
bottom of the the diary sheet rather than replacing the data which is
already in that sheet - I've tried to modify the macro but can't make
it work - any help would be very much appreciated!!!!! The current
macro is below:

Sub SearchForStringFeb()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 7
LSearchRow = 7

'Start copying data to row 7 in Sheet2 (row counter variable)
LCopyToRow = 7

While Len(Range("A" & CStr(LSearchRow)).Value) 0

'If value in column F = "n", copy entire row to Sheet2
If Range("F" & CStr(LSearchRow)).Value = "n" Then

'Select row in January to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Diary in next row
Sheets("Diary").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("February").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub


--
flurry
------------------------------------------------------------------------
flurry's Profile: http://www.excelforum.com/member.php...o&userid=34303
View this thread: http://www.excelforum.com/showthread...hreadid=542391

Pasting rows below the last row with data?
 

'Start copying data to row 7 in Sheet2 (row counter variable)
LCopyToRow = Worksheets("Diary").Cells(Rows.Count, "A").End(xlUp).Row + 1
If LCopyToRow < 7 Then LCopyToRow = 7

Mike F

"flurry" wrote in
message ...

I've got a macro which Tom and Don kindly helped me with - it searches
for 'n' in Column F and if it find it copies that whole row into a
sheet called 'Diary'. Only problem is I need it to copy it to the
bottom of the the diary sheet rather than replacing the data which is
already in that sheet - I've tried to modify the macro but can't make
it work - any help would be very much appreciated!!!!! The current
macro is below:

Sub SearchForStringFeb()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 7
LSearchRow = 7

'Start copying data to row 7 in Sheet2 (row counter variable)
LCopyToRow = 7

While Len(Range("A" & CStr(LSearchRow)).Value) 0

'If value in column F = "n", copy entire row to Sheet2
If Range("F" & CStr(LSearchRow)).Value = "n" Then

'Select row in January to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Diary in next row
Sheets("Diary").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("February").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub


--
flurry
------------------------------------------------------------------------
flurry's Profile:
http://www.excelforum.com/member.php...o&userid=34303
View this thread: http://www.excelforum.com/showthread...hreadid=542391

Pasting rows below the last row with data?
 

Hi flurry

try the following

LCopyToRow = Application.CountIf(Worksheets("Diary").Range("A:A "),
"<")



'Select row in January or February to copy
Rows(LSearchRow).Copy Destination:=Worksheets("Diary").Rows(LCopyToRow
+ 1)


'Move counter to next row
LCopyToRow = LCopyToRow + 1

Cheers Christian