Copy Data to differnt tabs quickly

[Lynette_LeJeune]

I am looking for a way to take a very large spread sheet and automatically have the data copied over to the appropriate tab.  Example find all the rows for Company Alpha and copy to its tab, Company Bravo and copy to its tab, Company Charlie and copy to its tab…

I tried macro copying and that did not work because it is not always the same number of rows and the same row number.  

I have attached a sample file.  Thanks for any help.

Using Windows platform and Excel 365 Apps

[#2 · Accepted solution]

@Lynette_LeJeune 

Make sure that G7 on the customer sheets equals G2 on the Aging sheet - either change G7 on all those sheets to Days, or change G2 on Aging to Days Invoiced. Otherwise, it gets much more complicated.

Then run the following macro:

Sub SplitData()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim m As Long
    Application.ScreenUpdating = False
    Set ws = Worksheets("Aging")
    m = ws.Range("E" & ws.Rows.Count).End(xlUp).Row
    ws.Range("O2").Value = "Bill to"
    For Each wt In Worksheets
        If wt.Name <> ws.Name Then
            ws.Range("O3").Value = wt.Name
            ws.Range("A2:L" & m).AdvancedFilter _
                Action:=xlFilterCopy, _
                CriteriaRange:=ws.Range("O2:O3"), _
                CopyToRange:=wt.Range("A7:L7")
        End If
    Next wt
    ws.Range("O2:O3").ClearContents
    Application.ScreenUpdating = True
End Sub

[#3]

@Hans Vogelaar you are brilliant ! If the customer names are the same on the aging as on the tabs does it matter how many I have?

[#4]

@Lynette_LeJeune 

No, it'll work as long as each sheet except for the Aging tab corresponds to a customer.

[#5]

@Lynette_LeJeune You tagged your posting "Office365". If this means you are an MS365 subscriber, you might want to consider the FILTER function. The only change you would need to make is enter the Customer name somewhere on the sheet that you already named. In the picture below you see that I did that in A1. Could well be in F2. No need for VBA.

[#6]

Thanks @Riny_van_Eekelen! I will try this one as well!

[#7]

This Option actually worked better for me as I was able to keep my formatting. Thank you both so much for your help!