If you are running the code from a control on a worksheet or a form then it
will be necessary to qualify your Cells references to the specific
worksheet.

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

Note the dots in front of the Cells keyword that tie the cells directly to
the sheet named in the With statement. Otherwise the Cells reference
reverts to the object containing the code and will either produce an error
or the wrong results.


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