Set MyRange = .range(range("A1"),Range("a1").End(xlDown)).resize (1, 9)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Susan" wrote in message
ups.com...
With Sheet2
.Visible = xlSheetVisible
.Cells.Clear
Set MyNewRange = .Range("a1:j500")
Range("a1").Select
End With

With Sheet1
Set MyRange = .Range("a1").End(xlDown).Offset(0, 8)
Range("a1").Select
End With

MyRange.Copy
MyNewRange.PasteSpecial Paste:=xlPasteAll

xxxxxxxxxxxxxxxxxxxxxxx

would somebody please hit me over the head
with a hammer & tell me why this macro
is giving me 500 rows copied of the one cell
located in column "I" in the last row of the
spreadsheet INSTEAD of copying the
range from a1:i<whatever.

obviously i am TELLING the macro to do
what it is doing; how do i tell it to do what
i want? "MyRange" is the problem. "My
NewRange" works fine (because it's not
variable!).

:c this macro was working perfectly well
but it was a very-newbie one that i am trying
to update with option explicit & making the
code more concise, etc.
thanks for your help & don't use a sledge-
hammer, please.
:)
susan