Search Range for string, then copy that cell to another worksheet

S

Shelly

On Worksheet1 I've got 6 sets of ranges (D34:D36, D50:D62, D66:D78, and so on
for a total of 6 ranges) that may contain the string "ACS" somewhere in the
cell.

I want to search these ranges, and copy both cells to the left of the cell
containing "ACS", the actual cell containing "ACS", and the cell directly to
the right to Worksheet2.

For example,
Worksheet1!D50 contains "ACS"
so
the cells in Worksheet1!B50:E50 are copied to a range in Worksheet2 (A8:D20)
specifically in the first row of the range A8:D8, because it is the first
instance of "ACS" found in the source cells.

When "ACS" is found again in Worksheet1 (say in cell D60),
the cells in Worksheet1!B60:E60 are copied to the 2nd row of the range in
Worksheet2 (A9:D9).

And so on until all the ranges in Worksheet1 have been searched.
 
M

merjet

Sub Macro1()
Dim c As Range
Dim rng As Range
Dim iRow As Integer

Set rng = Worksheets(1).Range("D34:D36, D50:D62, D66:D78")
iRow = 8
For Each c In rng
If InStr(c, "ACS") > 0 Then
Worksheets(1).Range("B" & c.Row & ":E" & c.Row).Copy _
Destination:=Worksheets(2).Range("A" & iRow & ":D" & iRow)
iRow = iRow + 1
End If
Next c
End Sub

Hth,
Merjet
 

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

Top