VBA - Paste Entire Row that meets criteria into Sheet2 without overwriting

[mawler023]

Program: Microsoft Excel 2010I'm working in Excel to develop a macro that searches column J in Sheet1 for "X" and returns the entire row to Sheet2. Here's the issue I'm running into: The macro is working without error in that the entire row is pasted into Sheet2 in Row 1. However, I'd like to have the macro not overwrite existing data in Sheet2 and paste data meeting the "X" criteria into the first empty row. I am familiar with the command below but I'm unsure how to utilize it in this context. [VBA]Range("A65536").End(xlUp).Row + 1[/VBA]Please help!! Here is my complete code:[VBA]Sub Copy_To_Another_Sheet_1() Dim FirstComplete As String Dim MyArr As Variant Dim Rng As Range Dim Rcount As Long Dim I As Long Dim NewSh As Worksheet With Application .ScreenUpdating = False .EnableEvents = False End With MyArr = Array("X") Set NewSh = Sheets("Sheet2") With Sheets("Sheet1").Range("J9:J250") Rcount = 0 For I = LBound(MyArr) To UBound(MyArr) Set Rng = .Find(What:=MyArr(I), _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then FirstComplete = Rng.Address Do Rcount = Rcount + 1 Rng.EntireRow.Copy NewSh.Range("A" & Rcount) Set Rng = .FindNext(Rng) Loop While Not Rng Is Nothing And Rng.Address FirstComplete End If Next I End With With Application .ScreenUpdating = True .EnableEvents = True End WithEnd Sub[/VBA]

[ashleyuk1984]

You'll want to use offset. something like this :-)

Range("A65536").End(xlUp).Offset(1, 0)).PasteSpecial Paste:=xlPasteValues

[pmyk]

'http://en.wikipedia.org/wiki/65536_(number)
65536 is the maximum number of spreadsheet rows supported by Excel 97, Excel 2000, Excel 2002 and Excel 2003.
Excel 2007 and 2010 support 1,048,576 rows.
So better use the following code to find the last row:

LastRecNum = Cells(Rows.Count, "D").End(xlUp).Row 'to go to last data cell inspite of blank cells