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" skrev i melding
...
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