Having trouble pasting to first empty cell

  • Thread starter Thread starter ToddEZ
  • Start date Start date
T

ToddEZ

I am trying to paste data to the right most empty cell on row 32, starting in
column B. Here is what I got so far:


Dim FirstEmpty6 As Range
Dim StartCell6 As Range

With ActiveSheet
Set StartCell6 = .Range("b32")
If IsEmpty(StartCell6.Value) Then
Set FirstEmpty6 = StartCell6
ElseIf IsEmpty(StartCell6.Offset(0, 1).Value) Then
Set FirstEmpty6 = StartCell6.Offset(0, 1)
Else
Set FirstEmpty6 = StartCell6.End(xlToRight).Offset(0, 1)

FirstEmpty6.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

End If
End With
 
you don't have anything to paste.

i used a1 in this example. watch for wordwrap in the posting.

Sub test()
Dim FirstEmpty6 As Range
Dim StartCell6 As Range

With ActiveSheet
Set StartCell6 = .Range("b32")
If IsEmpty(StartCell6.Value) Then
Set FirstEmpty6 = StartCell6
ElseIf IsEmpty(StartCell6.Offset(0, 1).Value) Then
Set FirstEmpty6 = StartCell6.Offset(0, 1)
Else
Set FirstEmpty6 = StartCell6.End(xlToRight).Offset(0, 1)
Range("A1").Copy
FirstEmpty6.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End With
End Sub
 
Hi Toddez

How about if you are pasting to one cell to the end of a column you
use this.

Take Care

Marcus

Sub PastetoLC()
Dim i As Integer
Dim lc As Integer

lc = Cells(1, 2).End(xlToRight).Column + 1
' Change A1 to relevant copy area.
Range("A1").Value Range(Cells(32, lc), Cells(32, lc)).Value


End Sub
 
Sorry try this.

Sub PastetoLC()
Dim i As Integer
Dim lc As Integer

lc = Cells(1, 2).End(xlToRight).Column + 1
Range(Cells(1, lc), Cells(1, lc)).Value = Range("A1").Value

End Sub
 
it copies whatever is in A1 to the first empty cell in row 32 for me.

Sub test()
Dim FirstEmpty6 As Range
Dim StartCell6 As Range

With ActiveSheet
Set StartCell6 = .Range("b32")
If IsEmpty(StartCell6.Value) Then
Set FirstEmpty6 = StartCell6
ElseIf IsEmpty(StartCell6.Offset(0, 1).Value) Then
Set FirstEmpty6 = StartCell6.Offset(0, 1)
Else
Set FirstEmpty6 = StartCell6.End(xlToRight).Offset(0, 1)
Range("A1").Copy ' change this
FirstEmpty6.PasteSpecial xlPasteValuesAndNumberFormats
End If
End With
End Sub
 
I'm still hacving problems. The cell I'm trying to copy is on a different
sheet. does this matter?

Dim FirstEmpty6 As Range
Dim StartCell6 As Range

With ActiveSheet
Set StartCell6 = .Range("b32")
If IsEmpty(StartCell6.Value) Then
Set FirstEmpty6 = StartCell6
ElseIf IsEmpty(StartCell6.Offset(0, 1).Value) Then
Set FirstEmpty6 = StartCell6.Offset(0, 1)
Else
Set FirstEmpty6 = StartCell6.End(xlToRight).Offset(0, 1)
Sheets("DAILY POLL").Range("c53:c62").Copy
FirstEmpty6.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End With
 
your code works for me.


Sub test()
Dim FirstEmpty6 As Range
Dim StartCell6 As Range

With ActiveSheet
Set StartCell6 = .Range("b32")
If IsEmpty(StartCell6.Value) Then
Set FirstEmpty6 = StartCell6
ElseIf IsEmpty(StartCell6.Offset(0, 1).Value) Then
Set FirstEmpty6 = StartCell6.Offset(0, 1)
Else
Set FirstEmpty6 = StartCell6.End(xlToRight).Offset(0, 1)
Sheets("DAILY POLL").Range("c53:c62").Copy
FirstEmpty6.PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End With
End Sub

Gary Keramidas
Excel 2003
 
Back
Top