Copy values from Column B to C, stopping at column B's end

[jimayers]

Hi - I am trying to write a code that searches for any value in a cell in column B. If it has a value to copy it to the cell next to it in column C. If it is blank I want it to leave it alone. Then move on to the next cell down in B and repeat..all the way down to my last cell in B.

Here is what I have:
Sub copypaste()


Dim i As Integer
Dim LastRow As Integer 'Long?
ActiveSheet.Range.Cells("B1").Select

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For i = 1 To LastRow


If ActiveSheet.Range.Cells(i, "B") = "" Then
Selection.Offset(1, 0).Select
ElseIf ActiveSheet.Range.Cells(i, "B") <> Nothing Then
Range.Cells(i, "B").Copy
Range.Cells(i, "C").Paste
End If
Next i

End With
End Sub

Thanks for your help - Jim A

 

[My Aswer Is This]

Try this:

Sub Search_Column_B()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    For i = 1 To Lastrow
        If Cells(i, 2) <> xlnull Then Cells(i, 3).Value = Cells(i, 2).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Perhaps I'm not understanding, but couldn't you just copy them all at once?

Sub CopyBC()
  Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy Destination:=Range("C1")
End Sub

 

[jimayers]

Thank you for your response.
But where there is data in column B, there are open cells to copy to in C.
where there is no data in B, there is in C.
I do not want to copy blank cells over data.

 

[jimayers]

Thanks - that worked well. So now I want to CUT instead of COPY

 

[My Aswer Is This]

Are you saying neither script given you here work? If they do not then you need to explain better.

where there is date in column B, there are open cells to copy to in C.
where there is no data in B, there is in C.
I do not want to copy blank cells over data.

 

[My Aswer Is This]

What do you mean "Cut" you said in Post #1

has a value to copy it to the cell next to it in column C

 

[My Aswer Is This]

Edit

 

[jimayers]

My Answer Is This - thanks...yours worked. I am not sure about the other, although I overlooked its simplicity. I just did not want to overwrite existing data in C with blanks in B.

 

[jimayers]

Edit

I'm all good. It works. Once data is copied I am eliminating column B anyway.