Macro
This is exactly what i needed.
Excellent work! Thanks!
I have one more question. Is there any way i can insert in this macro some
names that will belong to certain groups?
Ex:
Group 1
John
Mary
Group 2
Sam
George
Jim
What i need:
If i search John, then the result to be all rows containing "John"
If i search Group 1, than the result to be all rows containing "John" and
all rows containing "Mary"
The same with group 2. If i search by name to display only rows containing
that name. if i search by group, than the macro to display rows containing
all names in that group.
This thing will be so great.
Can this be done?
Thanks!!!
"Mike H" wrote:
Hi,
Now I understand. This is the correct line
c.Offset(, -1).Resize(, 7).Copy Sheets("Search").Cells(dlr, "a")
Note this steps 1 column to the left c.Offset(, -1) and then resizes to 7
columns from column A so A to g are copied
But now there's a problem because in the next line of your code
Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name
You write the sheet name to column G which will overwrite the last cell you
just copied. You will have to copy 6 columns or put the sheet name somewhere
else (say) column H
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"puiuluipui" wrote:
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!!
|