Here's the part of the code giving me trouble:

For Each rngItem In rngData
Set rngOut = rngAccounts.Find(What:=rngItem)

If rngOut Is Nothing Then
rngItem.Offset(0, 2).Value = "N/A"

Else
Set rngOut = rngOut.Offset(0, 1)
Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy _
Destination:=rngItem.Offset(0, 2)
End If
Next rngItem
Next mysht


What code do I add to it so that when it copies to the destination, it
will shift the entire rows down? I've been trying so hard but i can't
seem to work it out.
I tried Destination:=rngItem.Offset(0, 2).shift(x1down)
but it doesn't work. What should I do? Please please please help.
Thanks!!


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=393863

change

Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy _
Destination:=rngItem.Offset(0, 2)

to (two separate commands)

Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy

rngItem.Insert xlShiftDown

--
Regards,
Tom Ogilvy


"Sethaholic" wrote
in message ...

Here's the part of the code giving me trouble:

For Each rngItem In rngData
Set rngOut = rngAccounts.Find(What:=rngItem)

If rngOut Is Nothing Then
rngItem.Offset(0, 2).Value = "N/A"

Else
Set rngOut = rngOut.Offset(0, 1)
Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy _
Destination:=rngItem.Offset(0, 2)
End If
Next rngItem
Next mysht


What code do I add to it so that when it copies to the destination, it
will shift the entire rows down? I've been trying so hard but i can't
seem to work it out.
I tried Destination:=rngItem.Offset(0, 2).shift(x1down)
but it doesn't work. What should I do? Please please please help.
Thanks!!


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile:
http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=393863

thanks tom

i did as you said and i have this

Set rngOut = rngOut.Offset(0, 1)
Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy

rngItem.Offset(0, 2).Insert xlShiftDown


it works, but it only shifts the columns down. I want to shift the
entirerow down. I tried: rngItem.Offset(0, 2).entirerow.Insert
xlShiftDown
but it crashed on me. any ideas??


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=393863

Sub BBB()
Dim rng As Range
Dim rngItem As Range
Dim rngout As Range
Dim sAdd As String
Set rngItem = Range("A5")
Set rngout = Range("H3")
Set rngout = rngout.Offset(0, 1)
Set rng = Range(rngout, _
rngout.End(xlDown).End(xlToRight))
sAdd = rngItem.Address
rngItem.EntireRow.Resize(rng.Rows.Count).Insert xlShiftDown
rng.Copy Destination:=Range(sAdd).Offset(0, 2)
End Sub

Make sure the data you are copying will not be affected by inserting
entirerows.

--
Regards,
Tom Ogilvy




"Sethaholic" wrote
in message ...

thanks tom

i did as you said and i have this

Set rngOut = rngOut.Offset(0, 1)
Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy

rngItem.Offset(0, 2).Insert xlShiftDown


it works, but it only shifts the columns down. I want to shift the
entirerow down. I tried: rngItem.Offset(0, 2).entirerow.Insert
xlShiftDown
but it crashed on me. any ideas??


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile:
http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=393863