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

'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" <flurry.27w6oy_1147769101.8559@excelforum-nospam.com> wrote in
message news:flurry.27w6oy_1147769101.8559@excelforum-nospam.com...
>
> 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
>

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