copy rows based on wildcard text

[boltonguy2k]

hi

i have a sheet where column A contains names prefixed with "OT Dave", "FT Peter","AA John" etc.. the list is over 1000 names and id like to be able to pull one set of prefixed names ie: any row prefixed with "OT Dave", "OT bob" etc.. to a new sheet and be able to add the extra prefix names after that on a new sheet. so instead of having the 1000+ names i have only the prefixed named i need.

eg:

OT Dave
OT Peter
FT Peter
FT jane
FT sue
AA John
AA Lindsey

The list has 1000+ names on it.

I have tried various wildcard methods but none seem to work, is there a quick easy solution.

thanks

 

[onlyadrafter]

Hello,

have assumed all prefiix's are two letter. Also assumed that the sheet names for the prefix don't exist.

This code needs to be run when the activesheet is the one with the long list.

 Sub COPY_PREFIX()
    Application.ScreenUpdating = False
    MY_SHEET = ActiveSheet.Name
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        MY_SUFFIX = Left(Range("A" & MY_ROWS).Value, 2)
        For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count
            If Sheets(MY_SHEETS).Name = MY_SUFFIX Then
                GoTo CONT
            End If
        Next MY_SHEETS
        Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = MY_SUFFIX
CONT:
        Sheets(MY_SUFFIX).Select
        Sheets(MY_SHEET).Select
        Rows(MY_ROWS).Copy
        Sheets(MY_SUFFIX).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Next MY_ROWS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

is this anything near to your requirements?

 

[boltonguy2k]

Hello,

have assumed all prefiix's are two letter. Also assumed that the sheet names for the prefix don't exist.

This code needs to be run when the activesheet is the one with the long list.

 Sub COPY_PREFIX()
    Application.ScreenUpdating = False
    MY_SHEET = ActiveSheet.Name
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        MY_SUFFIX = Left(Range("A" & MY_ROWS).Value, 2)
        For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count
            If Sheets(MY_SHEETS).Name = MY_SUFFIX Then
                GoTo CONT
            End If
        Next MY_SHEETS
        Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = MY_SUFFIX
CONT:
        Sheets(MY_SUFFIX).Select
        Sheets(MY_SHEET).Select
        Rows(MY_ROWS).Copy
        Sheets(MY_SUFFIX).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Next MY_ROWS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

is this anything near to your requirements?

hi thanks for replying

that solution is genius and is what i am looking for

just one side note could it be adapted to just pick out certain prefixes ie: out of 10 prefixed names from the list.

eg:

FT andy
FT brian
FT mike
OT sue
Ot peter
OT dave
PR jor
PR gemma
PR leanne

so it puts FT andy,FT brian,FT mike in one tab with the full rows and then OT names in another tab.

ive just ran the script and it puts each seperate name in a seperate tab sorry if it was initially confusing.

 

[onlyadrafter]

Hello,

does this to the trick?

Sub COPY_PREFIX()
    Application.ScreenUpdating = False
    MY_SHEET = ActiveSheet.Name
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        MY_SUFFIX = Left(Range("A" & MY_ROWS).Value, 2)
        If MY_SUFFIX = "FT" Then
            MY_NEW_SHEET = "FT"
        Else
            MY_NEW_SHEET = "NOT FT"
        End If
        For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count
            If Sheets(MY_SHEETS).Name = MY_NEW_SHEET Then
                GoTo CONT
            End If
        Next MY_SHEETS
        Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = MY_NEW_SHEET
CONT:
        Sheets(MY_NEW_SHEET).Select
        Sheets(MY_SHEET).Select
        Rows(MY_ROWS).Copy
        Sheets(MY_NEW_SHEET).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Next MY_ROWS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Actually, having re-read your last post, am a bit confused?

Do you want every name with OT at the beginning in one tab, names with FT in another tab, names with AA in another tab?

If so, this is what my original code achieved.

Or did you want only all rows with OT Dave in one tab, then all rows with OT Peter in another tab?

Or did you only want to include specific prefixes, e.g. you many only want OT prefix, with the others ignored?

 

[boltonguy2k]

Hello,

does this to the trick?

Sub COPY_PREFIX()
    Application.ScreenUpdating = False
    MY_SHEET = ActiveSheet.Name
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        MY_SUFFIX = Left(Range("A" & MY_ROWS).Value, 2)
        If MY_SUFFIX = "FT" Then
            MY_NEW_SHEET = "FT"
        Else
            MY_NEW_SHEET = "NOT FT"
        End If
        For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count
            If Sheets(MY_SHEETS).Name = MY_NEW_SHEET Then
                GoTo CONT
            End If
        Next MY_SHEETS
        Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Name = MY_NEW_SHEET
CONT:
        Sheets(MY_NEW_SHEET).Select
        Sheets(MY_SHEET).Select
        Rows(MY_ROWS).Copy
        Sheets(MY_NEW_SHEET).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Next MY_ROWS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Actually, having re-read your last post, am a bit confused?

Do you want every name with OT at the beginning in one tab, names with FT in another tab, names with AA in another tab?

If so, this is what my original code achieved.

Or did you want only all rows with OT Dave in one tab, then all rows with OT Peter in another tab?

Or did you only want to include specific prefixes, e.g. you many only want OT prefix, with the others ignored?

Hi yes need all names with prefix to be in one tab and so on theres about 10 prefixes altogether "OT","FT", etc..

ive uploaded a dummy sheet to show you.

prefix.xlsx