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" <Jonathan@discussions.microsoft.com> wrote in message
news:9ABDFD83-1A31-461D-AA6C-00D4B7693BDD@microsoft.com...
> 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" <Jonathan@discussions.microsoft.com> wrote in message
> > 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
> > >
> > >
> >
> >
> >