Reputation: 71
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
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