i am new in VBA and i am blocked on my VBA code. what i am trying to do : On my database, inside the colmun M:M, if each cell from column M:M who contain "B1", it copy the line from the Sheet "Database" into another sheet ("Work"), make a filter on the Sheet ("Alloc") on the word "B1" and copy filtered cells from Sheet ("Alloc") to the Sheet ("work")
Please find my code :
Dim r As Range
Dim rw As Long, Cell As Range
For Each Cell In Sheets("Database").Range("M:M")
rw = Cell.Row
If UCase(Cell.Value) Like UCase("*B1*") Then
Cell.EntireRow.Copy
Sheets("Work").Select
Range("A1048576").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues
Sheets("Alloc").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$10000").AutoFilter Field:=1, Criteria1:= _
"B1"
Set r = Sheets("Alloc").Range("B2")
Do While r.Value <> ""
Range("N1048576").End(xlUp).Offset(1, 0).Value = r.Value
Set r = r.Offset(1)
Loop
Set r = Sheets("Alloc").Range("C2")
Do While r.Value <> ""
Range("O1048576").End(xlUp).Offset(1, 0).Value = r.Value
Set r = r.Offset(1)
Loop
Set r = Sheets("Alloc").Range("D2")
Do While r.Value <> ""
Range("P1048576").End(xlUp).Offset(1, 0).Value = r.Value
Set r = r.Offset(1)
Loop
Set r = Sheets("Alloc").Range("E2")
Do While r.Value <> ""
Range("Q1048576").End(xlUp).Offset(1, 0).Value = r.Value
Set r = r.Offset(1)
Loop
Sheets("Alloc").Select
Rows("1:1").Select
Selection.AutoFilter
End If
Next
My code is working, the only issue it's copy also data in sheet ("alloc") who are also fileted do you know how i can take only the filtered data from the sheet ("Alloc") into the sheet("work") ?
Thanks a lot for your help