I am looking for a way to copy/paste information from the third column of the name range to a different sheet based on a key word in the second column (columns in the name range are E:G). The information needs to be paste in a different sheet starting in B6. 2 of the columns could have the same key word but I need it to ignore the first column in the name range.
Current Code that grabs everything that has "Rotating" in a cell. (found it online in the process trying to make it work for me)
Sub Grab_Shift()
Dim dRef As String, rng As Range, ws1 As Worksheet
Dim x As Long, y As Long, ff As String, PasteTo As Range
dRef = "Rotating"
Set rng = Sheets("MSF").Range("NamenShift")
Set ws1 = Sheets("Sheet1")
x = rng.Rows.count
y = rng.Columns.count
Set cell = rng.Find(dRef, rng.Cells(x, y), , xlWhole)
If Not cell Is Nothing Then
ff = cell.Address
Do
Set PasteTo = ws1.Range("b" & Rows.count).End(xlUp).Offset(1)
PasteTo.Resize(, y).Value = rng.Rows(cell.Row - rng.Row + 1).Value
Set cell = rng.FindNext(cell)
Loop Until cell.Address = ff
End If
Set ws1 = Nothing: Set rng = Nothing
Set cell = Nothing: Set PasteTo = Nothing
End Sub
Bookmarks