Need to separate and group very large report by clients

Visitor

Hi I have a very large report that lists what I need to charge clients in Quickbooks for the month. It has each service call listed separately, what services, the amount of time, Date, who requested etc. I need to create a new workbook if possible, or just a new page, for each client that copies all of their rows/columns. I am sure there is a way to do this with functio

 

Here is an example of what I have now, and what I need it to be. 

Original Report: 

Cust_NoCust NameDateOrder NumberStart TimeTotal Min
1ABC Co1-1-20222225212:005
1ABC Co1-8-22225861:0045
1ABC Co1-18-222259910:007
8Pretend Co1-7-22258949:0048
8Pretend Co1-25-22259871:0036
25Ziggly Zoo1-25-22267984:0098

 

Need Separate Reports or Pages for each customer like this: 

Cust_NoCust NameDateOrder NumberStart TimeTotal Min
1ABC Co1-1-20222225212:005
1ABC Co1-8-22225861:0045
1ABC Co1-18-222259910:007

 

 

Cust_NoCust NameDateOrder NumberStart TimeTotal Min
8Pretend Co1-7-22258949:0048
8Pretend Co1-25-22259871:0036

 

I would prefer to have it copy the info vs move/delete it so that I can double check and have the original information as it is provided to me from the company but that is not a huge issue as I can always just save a copy before doing anything with functions etc. 

 

1 Reply

@Bumblina To create separate sheet you need VBA macro. Function can display output results only. Use below macro. Download the attached file and add more data then test.

Sub ProcessData()
Dim LastRow As Long, zLastRow As Long
Dim ws As Worksheet
Dim Rng As Range, zRng As Range, cel As Range

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = Range("A1:A" & LastRow)
    
    'Copy uniqe customer no to temp location Z1.
    Range("Z1").CurrentRegion.Clear
    Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z1"), Unique:=True
    zLastRow = Cells(Rows.Count, "Z").End(xlUp).Row
    Set zRng = Range("Z2:Z" & zLastRow)
    
    Application.ScreenUpdating = False
    
    'Create sheets with all customer no.
    For Each cel In zRng
        ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = cel
    Next cel
    
    DoEvents            'Release current operation for a while and give control to processor
    Sheet1.Activate     'Go back to first sheet
    
    'Copy data from first sheet to other sheets.
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=ws.Name
            ActiveSheet.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Sheets(ws.Name).Range("A1")
            ActiveSheet.Range("A1").CurrentRegion.AutoFilter
            ws.Columns.AutoFit
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
    'Clear/Empty memories
    Range("Z1").CurrentRegion.Clear
    Set Rng = Nothing
    Set zRng = Nothing
End Sub