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, LLC
www.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.