VLOOKUP type question to copy part of a row once the value is hound in a range

[Phantasm]

So I have values such as "SAW", "BP", "MILL", "LATHE" in the range of G2:O250 in WORKSHEET1.

What I want to do is create a new worksheet tab called "SAW" & have it import the any row from A:O on WORKSHEET1 that has that has the cell value of "SAW"in it. This will basically show me all jobs olny that have a SAW operation. I will do the same on other tabs for the other values.

Any help is appreaciated.

 

[Phantasm]

Awesome, one last thing...Im still getting columns P:U copied over, how can I get it for them not to come over?...Ive highlighted the output in the picture.

https://imgur.com/a/M5O0NaX

 

[Yongle]

replace

Me.[COLOR=#ff0000]Rows(r)[/COLOR].Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)

with

Me.[COLOR=#ff0000]Cells(r, 1).Resize(, 15)[/COLOR].Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)

 

[Phantasm]

replace

Me.[COLOR=#ff0000]Rows(r)[/COLOR].Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)

with

Me.[COLOR=#ff0000]Cells(r, 1).Resize(, 15)[/COLOR].Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)

You are a excel god. Thank you. I could have never have figured that out for myself. Now all I need is the time to understand what is doing what and how...Thanks again!

 

[Yongle]

ow all I need is the time to understand what is doing what

Private Sub SetRanges()
    Set DataRange = Me.Cells(1).CurrentRegion  [I][COLOR=#ff0000]Cells(1) is A1, [/COLOR][/I][I][COLOR=#ff0000]CurrentRegion is the area adjacent and contiguous (up to first empty row and first empty column) [/COLOR][/I]

    With DataRange
        Set OpsRange = .Offset(1, 6).Resize(.Rows.Count - 1, 9) [I][COLOR=#ff0000]DataRange Offset by 1 row and 6 columns (now begins in G2) [/COLOR][/I]
[I][COLOR=#ff0000]Then resized to one row smaller (makes up for offsetting 1 row down)  and 9 columns wide (columns G to O )[/COLOR][/I]
        LastRow = .Rows.Count
    End With
End Sub

[I][COLOR=#ff0000]This sub uses a Collection as a storage for the values in Columns G to O
[/COLOR][B]Ops.Add Cel, Cel[/B][COLOR=#ff0000]The first cel is the value being added, the 2nd cel is the "key" being added[/COLOR][/I]
[I][COLOR=#ff0000]Duplicate keys are not allowed and this is how the duplicates are filtered out [/COLOR][/I]
Private Sub CreateUniqueList()
    Set Ops = New Collection
    On Error Resume Next        'required to get unique list this way
    For Each Cel In OpsRange
        If Cel <> vbNullString Then Ops.Add Cel, Cel
    Next Cel
End Sub

Private Sub AddSheets()         'deletes old one first if it exists with same name
    Application.DisplayAlerts = False
    On Error Resume Next        'sheet name may be invalid or sheet may not exist
    For Each Op In Ops  [I][COLOR=#ff0000]for every unique value in columns G to O
ops is the collection, op the individual member[/COLOR][/I]
        SheetName = Op
        Sheets(SheetName).Delete [I][COLOR=#ff0000]deleted in case it already exists[/COLOR][/I]
        Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
        ws.Name = SheetName[I][COLOR=#ff0000][/COLOR][/I]
        Me.Rows(1).Copy
        ws.Cells(1).PasteSpecial (xlPasteColumnWidths)  [I][COLOR=#ff0000]cells(1) is A1[/COLOR][/I]
    Next
    Application.DisplayAlerts = True
End Sub

Private Sub CopyRows()
    On Error Resume Next
    For Each Op In Ops
        SheetName = Op
        Set ws = Sheets(SheetName)
        For r = 2 To LastRow
            Set MatchRange = OpsRange.Resize(1).Offset(r - 2)
            If WorksheetFunction.CountIf(MatchRange, SheetName) > 0 Then  [I][COLOR=#ff0000]looks through every row G:O looking for a match and copies the row if it matches current sheet[/COLOR][/I]
                Me.Cells(r, 1).Resize(, 15).Copy ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)  [I][COLOR=#ff0000]cell in column A resize 15 columns = A:O is copied to next available row in other   sheet[/COLOR][/I]
                ws.Cells(1, 1).AutoFilter  [COLOR=#ff0000]c[/COLOR][I][COLOR=#ff0000]ells(1,1) is A1[/COLOR][/I]
            End If
        Next r
    Next Op
End Sub