CODEWITHSUNDEEP
vbaexcel
Alexander Porcescu
Alexander Porcescu

Reputation: 71

If rows contain a word, identify and copy all cells from the column and copy to another sheet

I am trying to create a macro that will look for 4 different words in a row from Sheet ("Project Parts Requisitioning"). Once done, it will copy all cells from that 4 columns and will paste in sheet(GCC) in columns (A,D,E,O). I have a part of code but it doesn't look for the words, it just simply moves pre-defined cell to those columns.

I would really appreciate if you help me with this one.

With Sheets("GCC1") 
    lastrowGCC1 = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 
End With
arr1 = Array("K", "P", "Q", "AA")
arr2 = Array("A", "D", "E", "O")
For i = LBound(arr1) To UBound(arr1)
    With Sheets("Project Parts Requisitioning")
         lastrow = Application.Max(n, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
         .Range(.Cells(n, arr1(i)), .Cells(lastrow, arr1(i))).Copy
         Sheets("GCC1").Range(arr2(i) & lastrowGCC1).PasteSpecial xlPasteValues
    End With
Next
Application.CutCopyMode = False

Upvotes: 1

Views: 75

Answers (1)

idktho
idktho

Reputation: 109

try this

Sub testso()

arr1 = Array("K", "P", "Q", "AA")
arr2 = Array("A", "D", "E", "O")

For i = 0 To 3

    Sheets("GCC").Columns(arr2(i)) = Sheets("Project Parts Requisitioning").Cells.Find(arr1(i), LookIn:=xlValues, lookat:=xlPart, MatchCase:=True).EntireColumn.Value

Next i

End Sub

Upvotes: 1

Related Questions