Find Change In Cell Contents

[bobkap]

I have many rows of data that I need to break-up by customer and move to another worksheet so that each customer has their own sheet. I've figured out how to create the new sheets and move the data, but I'm lost as to how to copy rows of data for just one customer at a time. Here's an example of what my data looks like:

Customer	Category	Date	Code	Amount Paid
Bonneville Co.	A	12/22/2017	BG	$35.00
Bracketts	R	12/5/2017	BC	$48.00
Bracketts	A	12/12/2017	BC	$112.00
Campus Center	G	12/21/2017	CS	$120.00
Campus Center	G	12/22/2017	CS	$361.00
Campus Center	H	12/21/2017	CS	$220.00
Campus Center	D	12/12/2017	CS	$200.00
Campus Center	L	12/12/2017	CS	$200.00
Lowell Inc.	A	12/9/2017	LO	$110.00
Lowell Inc.	S	12/9/2017	LO	$110.00
Lowell Inc.	C	12/8/2017	LO	$110.00
Lowell Inc.	D	12/8/2017	LO	$110.00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

i thought I might use the sub-total function to separate the companies, but then I'm puzzled as to how I capture just one company's rows of data at a time to copy and paste to another sheet.

 

[Fluff]

How about

Sub AddSht_FltrPaste()
   
   Dim Cl As Range
   Dim UsdRws As Long
   Dim OSht As Worksheet
   
Application.ScreenUpdating = False
   
   Set OSht = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   UsdRws = OSht.Range("A" & Rows.Count).End(xlUp).Row
   OSht.Range("A1").AutoFilter
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2:A" & UsdRws)
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            OSht.Range("A1:G" & UsdRws).AutoFilter Field:=1, Criteria1:=Cl.Value
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = Cl.Value
            OSht.Range("A1:A" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
               Sheets(Cl.Text).Range("A1")
         End If
      Next Cl
   End With
   OSht.Range("A1").AutoFilter
   
End Sub

This will add the sheets & copy the relevant data over.
Change sheet name in red to suit

 

[bobkap]

WOW! Nice! That works perfectly. Thanks very much!!

I've never done something like your "CreateObject("scripting.dictionary")" If you don't mind, can you please tell me what that does? Where does that scripting.dictionary exist? OR, is that some kind of function I need to learn?

 

[Fluff]

It's part of the Microsoft Scripting runtime library, if that's any help
You can a good introduction to dictionaries here
https://excelmacromastery.com/vba-dictionary/