Unable to paste to worksheet

  • Thread starter Thread starter Patrick C. Simonds
  • Start date Start date
P

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
 
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.
 
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
 
Back
Top