Hi Jonathan

First: Some of your cells and ranges have a sheet address, some doesn't.
Which makes it very fragile.

Second, you select and activate a lot. Don't, it's slow, ugly and it moves
the cursor so the user end up somewhere else than she were before.

See if this make sense:

Sub Transfer()
Dim rSource As Range
Dim rTarget As Range

Set rSource = Worksheets(1).Cells(65000, 2).End(xlUp)
Set rTarget = Worksheets(2).Cells(65000, 2).End(xlUp).Offset(1, 0)

rTarget.Value = rSource.Value

Set rSource = Nothing
Set rTarget = Nothing

End Sub


HTH. Best wishes Harald


"Jonathan" <Jonathan@discussions.microsoft.com> skrev i melding
news:0C61ECF2-CC3F-4CCA-A8C2-541B37F025F9@microsoft.com...
> I am trying to copy the value of the last cell with a value on sheet 1
column
> B to the first blank cell on sheet 2 column B, but I keep getting errors
in
> my script (please see below) am I missing something really obvious? (Error
> Message "run-time error 1004")
>
> Worksheets(2).Cells(1, 1).Value = "CB Barcode"
> Worksheets(2).Cells(1, 2).Value = "8 Digit Code"
> Worksheets(2).Cells(2, 2).Value = " "
> Range("B2").Select
> Selection.ClearContents
> Worksheets(1).Select
> Range("B1").Select
> ActiveCell.SpecialCells(xlLastCell).Select
> Selection.Copy
> Worksheets(2).Select
> Range("B2").Select
> ActiveSheet.Paste
>
> Tia
>
> Jonathan
>
>