VBA code to split worksheet by invoice type

pbolali this is exactly the reason you don't want to stack PivotTables or Tables vertically. They can grow. It's why I recommended they each go on their own sheet.

That being said, it's apparent you want a VBA solution. The below code will do what you want. Make sure you change the sheet names to what you have. Please note I did Table the data and referenced it as such in the code. More importantly, in your 'Date Paid' column you have some data which will give you an Overflow error. They are formatted as date, yet they reference the 'Budged Value' and add 30, seemingly arbitrarily. You can see this in cell N29. There were only 9 cells with this formula. If those aren't removed, the below code will fail. I did not code around it because I believe the user should know and clean their data first, not rely on code to do so.

Sub SplitInvoiceData()
    
    Dim Remarks As New Collection
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim Table As ListObject
    Dim Column As Long
    Dim Row As Long
    Dim Index As Long
    Dim NewRow As Long
    Dim RemarkIndex As Long
    Dim Remark As Variant
    Dim Output As Variant
    
    Const SourceTableName As String = "Table1"
    Const BufferRows As Long = 2
    
    Set SourceSheet = ThisWorkbook.Worksheets("Sheet1")
    Set TargetSheet = ThisWorkbook.Worksheets("VBA Output")
    Set Table = SourceSheet.ListObjects(SourceTableName)
    
    For RemarkIndex = 1 To Table.ListRows.Count
        On Error Resume Next
        Remarks.Add Table.ListRows(RemarkIndex).Range(1, 1).Value, Table.ListRows(RemarkIndex).Range(1, 1).Value
        On Error GoTo 0
    Next RemarkIndex
    
    TargetSheet.Cells.Clear
    
    For Each Remark In Remarks
        
        Row = 0
        
        For Index = 1 To Table.ListRows.Count
        
            If Table.ListRows(Index).Range(1, 1) = Remark Then
            
                Row = Row + 1
                If IsEmpty(Output) Then
                    ReDim Output(1 To 16, 1 To Row)
                Else
                    ReDim Preserve Output(1 To 16, 1 To Row)
                End If
                For Column = 1 To 16
                    Output(Column, Row) = Table.ListRows(Index).Range(1, Column).Value
                Next Column
                
            End If
            
        Next Index
        
        NewRow = TargetSheet.Cells(TargetSheet.Rows.Count, 1).End(xlUp).Row + BufferRows + 1
        
        'Header
        TargetSheet.Cells(NewRow, 1).Resize(1, 16).Value = Table.HeaderRowRange.Value
        Table.HeaderRowRange.Copy
        TargetSheet.Cells(NewRow, 1).Resize(1, 16).PasteSpecial xlPasteFormats
        
        'Data
        TargetSheet.Cells(NewRow + 1, 1).Resize(Row, 16).Value = Application.Transpose(Output)
        Table.ListRows(1).Range.Copy
        TargetSheet.Cells(NewRow + 1, 1).Resize(Row, 16).PasteSpecial xlPasteFormats
        
        'Total formulas
        With TargetSheet.Cells(NewRow + Row + 1, 1).Resize(1, 16)
            .Interior.ColorIndex = 37
            .Cells(1, 1).Value = "Total"
            .Cells(1, 10).Resize(1, 7).Formula = "=SUM(J" & NewRow & ":J" & NewRow + Row & ")"
        End With
        Application.CutCopyMode = False
        
    Next Remark
    
    TargetSheet.Cells.EntireColumn.AutoFit
    
End Sub

There's no real error handling or performance boosts here, so on larger data sets this might not be ideal or be the fastest. If you were going to have a larger data set I'd recommend using [additional] arrays for housing all of the data.

HTH

Zack Barresse 

I played with the pivot tables all night..., I had an overlap or rows issue though. Please find attached the expected final output for report 1.

The key is actually the invoice numbers (grouping similar invoices). But i did created a remark column to simplify that.

Please see if the pivot table can produce this output. If not please program this project for me please.