Aug 31 2021 07:12 AM
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
Aug 31 2021 08:04 AM
SolutionMake 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
Aug 31 2021 08:18 AM
Aug 31 2021 08:21 AM
No, it'll work as long as each sheet except for the Aging tab corresponds to a customer.
Aug 31 2021 08:42 AM
@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.
Aug 31 2021 09:31 AM
Sep 01 2021 10:54 AM
Aug 31 2021 08:04 AM
SolutionMake 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