'Loop within a Loop'
 

I am familiar with and use loops on a regular basis but have run into
something I can't figure out.

What I am trying to do at a high level is copy data from one worksheet (old)
to a different worksheet (new).
In 'old' worksheet if column k in any row equals X then copy and paste the
data from columns c,d,e,f of that same row to the same location (row and
column) in the 'new worksheet'.
I started to create a loop to copy data if column K equals X but not sure
how to paste data over to 'new' worksheet.

Thanks in advance for any assistance,
Rob

'Loop within a Loop'
 

Hi Rob

This example will copy to the sheet next to it
Try it on a copy of your workbook

Sub test_1()
For Each cell In Range("K1:K100")
If cell = "X" Then
With Range(Cells(cell.Row, "C"), Cells(cell.Row, "F"))
.Parent.Next.Range(.Address) = .Value
End With
End If
Next cell
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Rob Moore" <Rob wrote in message ...
I am familiar with and use loops on a regular basis but have run into
something I can't figure out.

What I am trying to do at a high level is copy data from one worksheet (old)
to a different worksheet (new).
In 'old' worksheet if column k in any row equals X then copy and paste the
data from columns c,d,e,f of that same row to the same location (row and
column) in the 'new worksheet'.
I started to create a loop to copy data if column K equals X but not sure
how to paste data over to 'new' worksheet.

Thanks in advance for any assistance,
Rob

'Loop within a Loop'
 

Try something like this...

Sub CopyStuff()
Dim wksCopyFrom As Worksheet
Dim wksCopyTo As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range
Dim rngPaste As Range

Set wksCopyFrom = Sheets("Sheet1")
Set wksCopyTo = Sheets("Sheet2")
Set rngToSearch = wksCopyFrom.Columns("K")

Set rngFound = rngToSearch.Find("x")
If rngFound Is Nothing Then
MsgBox "Not Found"
Else
Set rngFirst = rngFound
Do
Set rngPaste = wksCopyTo.Range(rngFound.Address)
rngFound.Offset(0, -1).Copy rngPaste.Offset(0, -1)
rngFound.Offset(0, -2).Copy rngPaste.Offset(0, -2)
rngFound.Offset(0, -3).Copy rngPaste.Offset(0, -3)
rngFound.Offset(0, -4).Copy rngPaste.Offset(0, -4)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
End If

End Sub

You just need to change the offsets to select the columns to copy from...
--
HTH...

Jim Thomlinson


"Rob Moore" wrote:

I am familiar with and use loops on a regular basis but have run into
something I can't figure out.

What I am trying to do at a high level is copy data from one worksheet (old)
to a different worksheet (new).
In 'old' worksheet if column k in any row equals X then copy and paste the
data from columns c,d,e,f of that same row to the same location (row and
column) in the 'new worksheet'.
I started to create a loop to copy data if column K equals X but not sure
how to paste data over to 'new' worksheet.

Thanks in advance for any assistance,
Rob