Having trouble pasting to first empty cell

[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

 

[Gary Keramidas]

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

 

[ToddEZ]

Unfortunately, that did not work...

Any other ideas

 

[marcus]

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

 

[marcus]

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

 

[Gary Keramidas]

what are you trying to paste?

 

[Gary Keramidas]

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

 

[ToddEZ]

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

 

[Gary Keramidas]

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