On Feb 13, 5:39*pm, Chip Pearson wrote:
The problem likely lies in the way you are using Cells.

Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4))

Here, you are trying create a range on worksheet 'Raw Data' with

Sheets("Raw Data").Range

but you have not instructed the Cells method which worksheet they are
to refer.

Cells(7, Mycol)

This Cells call does not necessarily point to sheet 'Raw Data'. It
might, if Raw Data is the active sheet. But if any other sheet is
active, it will point to that sheet, and thus you are attempting to
create a range on Raw Data using ranges on another sheet. This
obviously won't do, so you get an error. If the code is in one of the
Sheet modules, the Cells reference will point to that worksheet,
regardless of what sheet is active.

A much better way is to use a With statement and qualify all the
references to Raw Data with leading periods.

With Sheets("Raw Data")
* * * * .Range(.Cells(7, Mycol), .Cells(397, Mycol + 4)).Copy_
* * * * * * * *Destination:=whatever
End With

Pay close attention to the leading periods. They are very important
because they point whatever follows them to the object in the With
statement.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

On Sat, 13 Feb 2010 12:08:03 -0800 (PST), Charles

wrote:
HI,

The fowling code does not work as designed

Sheets("Raw Data").Range(Cells(7, Mycol), Cells(397, Mycol + 4)).Copy
Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng)

However if I changed it to
Sheets("Raw Data").Range("B7:F397").Copy
Destination:=MyWkBook.Sheets("Raw Data").Range(Myrng)

It works.

MyCol should = 2 because a Find determined that the item I was looking
for is in column 2.

Any help with this problem would be appreciated.

Thanks to all who replayed, sorry for the late feedback. I was taken
down with the "Flu".
Chip's answered my problem, but also learned from the the other
replays.

Thanks again.