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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Bob Phillips wrote:
> Set MyRange = .range(range("A1"),Range("a1").End(xlDown)).resize(1, 9)

thank you very much for that nice
soft tap on the head.
susan