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)