I'm still not getting consistent paste results with code I've been trying to
work out, so I was looking to tighten up the copy formula, but am getting an
error message.

Dim r As Range, r1 As Range
With Worksheets("IHSF DATA ENTRY") ' the source, columns a-j
Set r = .Cells(Rows.Count, 1).End(xlUp).Row
End With
With Worksheets("MERGE DATA IHSF") ' the destination; columns d-m
Set r1 = .Range("D2").CurrentRegion
r1.Offset(1, 0).Copy r(2)
End With

Thanks... suzleigh

Line... "Set r = .Cells(Rows.Count, 1).End(xlUp).Row"

Should be... Set r = .Cells(.Rows.Count, 1).End(xlUp)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Suzanne"
wrote in message
I'm still not getting consistent paste results with code I've been trying to
work out, so I was looking to tighten up the copy formula, but am getting an
error message.

Dim r As Range, r1 As Range
With Worksheets("IHSF DATA ENTRY") ' the source, columns a-j
Set r = .Cells(Rows.Count, 1).End(xlUp).Row
End With
With Worksheets("MERGE DATA IHSF") ' the destination; columns d-m
Set r1 = .Range("D2").CurrentRegion
r1.Offset(1, 0).Copy r(2)
End With
Thanks... suzleigh

No... still doesn't work.


"Jim Cone" wrote:

>
> Line... "Set r = .Cells(Rows.Count, 1).End(xlUp).Row"
>
> Should be... Set r = .Cells(.Rows.Count, 1).End(xlUp)
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>
>
> "Suzanne"
> wrote in message
> I'm still not getting consistent paste results with code I've been trying to
> work out, so I was looking to tighten up the copy formula, but am getting an
> error message.
>
> Dim r As Range, r1 As Range
> With Worksheets("IHSF DATA ENTRY") ' the source, columns a-j
> Set r = .Cells(Rows.Count, 1).End(xlUp).Row
> End With
> With Worksheets("MERGE DATA IHSF") ' the destination; columns d-m
> Set r1 = .Range("D2").CurrentRegion
> r1.Offset(1, 0).Copy r(2)
> End With
> Thanks... suzleigh
>

It works for me.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Suzanne"
wrote in message
No... still doesn't work.

The formula copied the first row of MERGE DATA IHSF (nothing else)

"Jim Cone" wrote:

>
> It works for me.
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
>
>
> "Suzanne"
> wrote in message
> No... still doesn't work.
>
>
>

Add the two Msgbox lines to the code to get a reading on what is going on...
Also, technically there are no "formulas" in the posted code.
'--
Dim r As Range, r1 As Range
With Worksheets("IHSF DATA ENTRY") ' the source, columns a-j
Set r = .Cells(.Rows.Count, 1).End(xlUp)
End With
With Worksheets("MERGE DATA IHSF") ' the destination; columns d-m
Set r1 = .Range("D2").CurrentRegion

MsgBox "D2 Current region is :" & r1.Address & vbCr & _
"The offset area :" & r1.Offset(1, 0).Address & " will be copied. "

r1.Offset(1, 0).Copy r(2)
End With
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Suzanne"
wrote in message
The formula copied the first row of MERGE DATA IHSF (nothing else)