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

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

This is picking up the next empty cell the first time i paste over but then
it starts to leave blanks between each paste.

"Paul Lautman" wrote:

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

You say "This is". Whould you care to post what the current "This" looks
like?

enyaw wrote:
This is picking up the next empty cell the first time i paste over
but then it starts to leave blanks between each paste.

"Paul Lautman" wrote:

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

Sub copy()
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 = lc(sh2).Row
For i = 1 To 35
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").ClearContents
End Sub

"Paul Lautman" wrote:

You say "This is". Whould you care to post what the current "This" looks
like?

enyaw wrote:
This is picking up the next empty cell the first time i paste over
but then it starts to leave blanks between each paste.

"Paul Lautman" wrote:

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

The problem is that column A is hidden so I need to copy into the next empty
cell in column B. Do you know how I could do this??

"enyaw" wrote:

Sub copy()
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 = lc(sh2).Row
For i = 1 To 35
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").ClearContents
End Sub

"Paul Lautman" wrote:

You say "This is". Whould you care to post what the current "This" looks
like?

enyaw wrote:
This is picking up the next empty cell the first time i paste over
but then it starts to leave blanks between each paste.

"Paul Lautman" wrote:

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