VBA To Find Key Words Column A & Then Cut & Paste That Row & All Rows Below To New Sheet. Row# Varies & Then Repeat On New Sheet

[Galapagos15]

I receive broker statements that were imported from a PDF and come to me in one excel sheet but each "Product" grouping should be on a different tab. Basically, what I’m trying to do is “Find” key words in column “A” such as 1-50 Group and then cut and paste that row that contains the key word and all of the rows below it to another tab and then "Find" another key word in that new sheet such as 51+ Group and then cut and paste that row that contains the key word and all of the rows below it to another tab and then repeat.

The macro below works but the row numbers where the key words are located can vary for each broker therefore, if the sheet didn’t have these key words in the exact same rows it doesn’t work. I’m thinking the Row statement highlighted in green needs to be a variable statement but I’m not sure how to write it.

Example of an Original Statement That Is On One Sheet but Indiv Accts, 1-50 Group, 51+ Group and Off Exchange need to be on different sheets.

Column A B C

Individual Accounts Account# Bill Month
1 AB 01/01/2015
2 AB 01/01/2015
3 AB 01/01/2015
4 AB 01/01/2015
5 AB 01/01/2015
6 AB 01/01/2015
7 AB 01/01/2015
8 AB 01/01/2015
9 AB 01/01/2015
10 AB 01/01/2015
11 AB 01/06/2015
1-50 Group Commission Current Contracts Bill Month
12 BC 01/01/2015
13 BC 12/01/2014
14 BC 12/01/2014
15 BC 02/01/2015
16 BC 01/01/2015
17 BC 02/01/2015
18 BC 12/01/2014
19 BC 12/01/2014
20 BC 02/01/2015
51+ Group Commission PCPM Bill Month
21 DE 01/01/2015
22 DE 02/01/2015
Off Exchange Commission Withheld Account# Bill Month
23 FG 12/01/2014
24 FG 01/01/2015
25 FG 02/01/2015

My Macro that only works for the scenario above but it doesn't work for other statements because the "Products" fall in different rows.

'

' Macro2 Macro

'



'

Columns("A:A").Select

Selection.Find(What:="1-50 Group Commission", After:=ActiveCell, LookIn:= _

xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

xlNext, MatchCase:=False, SearchFormat:=False).Activate

Rows("13:13").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Cut

Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Paste

Columns("A:A").Select

Selection.Find(What:="51+ Group Commission", After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate

Rows("11:11").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Cut

Sheets.Add After:=Sheets(Sheets.Count)

Range("A1").Select

ActiveSheet.Paste

Columns("A:A").Select

Selection.Find(What:="Off Exchange Commission Withheld", After:=ActiveCell, LookIn:=xlFormulas, _

LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False).Activate

Rows("4:4").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Cut

Sheets.Add After:=Sheets(Sheets.Count)

Range("A1").Select

ActiveSheet.Paste

Range("E11").Select

End Sub