Copy Error

S

Suzanne

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
 
J

Jim Cone

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
 
J

Jim Cone

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Paste not working again 0
VBA Coding Help for Beginner 0
Array with many cells from a row but not in order or together 11
Resize a row but copy but skip columns I don't want to copy 6
Copy row info but not in normal column sequence 6
Subtotal by VBA 5
Eliminate blanks while copy row and transpose 11
Open four Wbooks, copy four columns from each to Master Wbook 16

Top