Hi,
What i want to do is copy A to G of the row i find. This is all.
I don't want another row.
If i search for "John" and the macro finds "John" in B5, then the macro to
copy A5:G5.
I tried your ideea and it's fine, but although copy the row above, doesn't
copy from A to G. It copy from B to H.

Thanks!


"Mike H" wrote:

Hi,

OK so you find some value in column B and then copy columns A to G of that
row. What you want to do if I understand correctly is coppy A to G of the row
you find AND A to g of the previous row. is that correct? If so i gave you
the correct answer last time.

Replace this row
c.Resize(, 7).Copy Sheets("Search").Cells(dlr, "a")

With this one
c.Offset(-1).Resize(2, 7).Copy Sheets("Search").Cells(dlr, "a")

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"puiuluipui" wrote:

Hi, is not working. I haven't explained exactly the first time. Below is the
entire code. I guess is easy for you to see the entire code than my
explanation.

The code copy 6 cells starting from B column and display them in "search"
sheet. The seven cell displayed in search sheet is the sheet's name from
where cells were copied. The macro ignore from search, sheets : planning and
search.

This is the entire code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$H$1" Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheets("Search")
lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 4)
.Range("a3:g" & lr).ClearContents
what = UCase(.Range("H1"))
On Error Resume Next
mydays = Array("LUNI", "MARTI", "MIERCURI", "JOI", "VINERI")
For Each sh In mydays

With Worksheets(sh).Range("b5:b1000")
Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do

dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1
c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a")
Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

Next sh
lr = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("a3:g" & lr).Borders.LineStyle = xlNone
End With
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub


Thanks!!



"puiuluipui" wrote:

Hi, i have this line of code that copy 6 cells to the right. Can this be
modified to copy the cell before those 6 cells too?

dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1
c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a")
Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name

The word this macro is searching for is in B and this is why copied range is
B:G
This code copy from range B:G.

Can this code be made to copy from range A:G?
Thanks!!