I'm Confused........................................

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" <[email protected]> skrev i melding
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> skrev i melding
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> skrev i melding
news:%[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
> >