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

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

It runs okay for me. What line do you get the error on?

--
HTH

Bob Phillips

"Jonathan" wrote in message
...
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

Hi Bob

I get the error messages at line 11 Range("B2").Select

"Bob Phillips" wrote:

It runs okay for me. What line do you get the error on?

--
HTH

Bob Phillips

"Jonathan" wrote in message
...
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

Oh, btw I am running this code from a command button, but that should effect
it should it?

"Bob Phillips" wrote:

It runs okay for me. What line do you get the error on?

--
HTH

Bob Phillips

"Jonathan" wrote in message
...
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

Excel 97 ? Set the button's "take focus on click" to False.

But read my other post regarding all the Select stuff you do.

HTH. best wishes Harald

"Jonathan" skrev i melding
...
Oh, btw I am running this code from a command button, but that should
effect
it should it?

"Bob Phillips" wrote:

It runs okay for me. What line do you get the error on?

--
HTH

Bob Phillips

"Jonathan" wrote in message
...
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

Jonathon, generally speaking a CommandButton_Click event code can only deal
with ranges on the same sheet as the button. In your case, with the button
on Sheet2, but wanting to involve ranges on Sheet1, put your macro code in a
general module and Call it from the CommandButton_Click event. Also, heed
Harold's advice about selecting objects. His code, in a general module, will
put the value on Sheet1 as you wanted. Only do a Copy/Paste if you need the
cell formatting also transfered.

Mike F
"Jonathan" wrote in message
...
Oh, btw I am running this code from a command button, but that should
effect
it should it?

"Bob Phillips" wrote:

It runs okay for me. What line do you get the error on?

--
HTH

Bob Phillips

"Jonathan" wrote in message
...
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

Doh ! The code is in the worksheet module. Of course. Thanks.

The code CAN to a certain degree deal with remote ranges if properly
addressed. But it shouldn't, so I second your advice.

Best wishes Harald

"Mike Fogleman" skrev i melding
...
Jonathon, generally speaking a CommandButton_Click event code can only
deal
with ranges on the same sheet as the button. In your case, with the button
on Sheet2, but wanting to involve ranges on Sheet1, put your macro code in
a
general module and Call it from the CommandButton_Click event. Also, heed
Harold's advice about selecting objects. His code, in a general module,
will
put the value on Sheet1 as you wanted. Only do a Copy/Paste if you need
the
cell formatting also transfered.

Mike F

Yes, the fact that it is in a worksheet module does affect it.
Worksheets(2).Select
Worksheets(2).Cells(1, 1).Value = "CB Barcode"
Worksheets(2).Cells(1, 2).Value = "8 Digit Code"
Worksheets(2).Cells(2, 2).Value = " "
Worksheets(2).Range("B2").Select
Selection.ClearContents
Worksheets(1).Select
Worksheets(1).Range("B1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.Copy
Worksheets(2).Select
Worksheets(2).Range("B2").Select
ActiveSheet.Paste

By qualifying all your range references, it avoids confusion. the code is
probably in worksheets(1), so when you just say Range("B2").Select that is
the same as saying worksheets(1).Range("B2").Select. Since you just
selected worksheets(2), you can't select a cell on worksheets(1) and you get
the error. By specifying worksheets(2) (which is your actual intent), excel
understands exactly what you want. In a normal module, an unqualified range
reference refers to the activesheet, but in a worksheet module, an
unqualified range reference refers to the sheet containing the code.

But, as Harald said, I strongly endorse not using select. It is much faster
and cleaner.

--
Regards,
Tom Ogilvy


"Jonathan" wrote in message
...
Oh, btw I am running this code from a command button, but that should
effect
it should it?

"Bob Phillips" wrote:

It runs okay for me. What line do you get the error on?

--
HTH

Bob Phillips

"Jonathan" wrote in message
...
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