enyaw wrote:
I need to paste information into another sheet but i need to paste it
into the next empty row. This is the formaula i have so far. Can
anyone help? Sub copyData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng2 As Range, j As Long, i As Long
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set rng2 = sh2.Cells(Rows.Count, 1).End(xlUp)(2)
j = 1
For i = 1 To 31
If IsNumeric(sh1.Cells(i, 1)) Then
If sh1.Cells(i, 1) 0 Then
sh1.Cells(i, 1).EntireRow.Copy
rng2(j).PasteSpecial xlValues
rng2(j).PasteSpecial xlFormats
j = j + 1
End If
End If
Next i
Range("B2:B30").Select
Selection.ClearContents
Range("B2").Select

End Sub

First let me mention that you can change:
Range("B2:B30").Select
Selection.ClearContents
Range("B2").Select
to
Range("B2:B30").ClearContents

For the next empty row, I use the function below to tell me the extent of
the data thus

j = lc(sh2).row

Function lc(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error GoTo blanksheet

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set lc = ws.Cells(LastRow&, LastCol%)
Exit Function
blanksheet:
Set lc = ws.Cells(1, 1)

End Function