Unable to paste to worksheet

[Patrick C. Simonds]

Can anyone tell me why the line "ActiveSheet.Paste" is not functioning? I
want to paste the contents of the clipboard onto the worksheet.

Sub Paste_Data()

Dim BCell, NBCell
Dim rng
Set rng = Cells(ActiveCell.Row, 1)

Application.ScreenUpdating = False

Range("B7").Select

For I = 1 To 65536
If ActiveCell.Value = Empty Then
BCell = "B" & CStr(I - 1)
NBCell = "B" & CStr(I - 2)
Exit Sub
Else
Range("B" & CStr(I + 1)).Select
End If
Next I

ActiveSheet.Paste

Application.ScreenUpdating = True

End Sub

 

[Don Guillett]

Start over and tell us what you are trying to do. Why fill the entire
column?????

 

[Patrick C. Simonds]

The bulk of the code is designed to take me to the first empty cell in the
column (below B7), that is where I need to be before pasting the data from
the clipboard into the worksheet. And the "ActiveSheet.Paste" should paste
the data in the worksheet from that point.

 

[Per Jessen]

Hi

This should do what you need:

Sub Paste_Data()
Dim PasteTo As Range

If Range("B8").Value = "" Then
Set PasteTo = Range("B8")
Else
Set PasteTo = Range("B7").End(xlDown).Offset(1, 0)
End If
ActiveSheet.Paste PasteTo
End Sub

Regards,
Per