Finding Credit and Debit Data and Creating Pivot

[littleiitin]

Hi Prabhu,

I have named this Data sheet name as "Raw Data", Created Two sheets "Credit" and "Debit"

Now do the Same:

Create a Module and Paste Below Code and Run First Code "CreatingPositiveNegativeSheet":

Sub CreatingPositiveNegativeSheet()
    
    ThisWorkbook.Worksheets("Credit").UsedRange.ClearContents
    ThisWorkbook.Worksheets("Debit").UsedRange.ClearContents
    With ThisWorkbook.Worksheets("Raw Data")
        If .AutoFilterMode = True Then .AutoFilterMode = False
        .Range("A1").CurrentRegion.Offset(1).AutoFilter Field:=8, Criteria1:="<0", _
        Operator:=xlAnd
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Worksheets("Credit").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        
        If .AutoFilterMode = True Then .AutoFilterMode = False
        .Range("A1").CurrentRegion.Offset(1).AutoFilter Field:=8, Criteria1:=">=0", _
        Operator:=xlAnd
        .UsedRange.SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Worksheets("Debit").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        If .AutoFilterMode = True Then .AutoFilterMode = False
        Application.CutCopyMode = False
        
        Call CreditSheetPivote
        Call DebitSheetPivote
    End With
End Sub

Sub CreditSheetPivote()
    
    Dim rngPivote   As Range
    Dim rngDest     As Range
   With ThisWorkbook.Worksheets("Credit")
        Set rngPivote = .Range("A2").CurrentRegion.Offset(1)
        Set rngDest = .Range("T3")
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        rngPivote, Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=rngDest, TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion10
        
        With .PivotTables("PivotTable2").PivotFields("VENDOR")
            .Orientation = xlRowField
            .Position = 1
        End With
        .PivotTables("PivotTable2").AddDataField .PivotTables( _
            "PivotTable2").PivotFields("BOOKED"), "Sum of BOOKED", xlSum
    End With
    ThisWorkbook.ShowPivotTableFieldList = False


End Sub

Sub DebitSheetPivote()
    
    Dim rngPivote   As Range
    Dim rngDest     As Range
    
   With ThisWorkbook.Worksheets("Debit")
        Set rngPivote = .Range("A2").CurrentRegion.Offset(1)
        Set rngDest = .Range("T3")
        ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        rngPivote, Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=rngDest, TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion10

        With .PivotTables("PivotTable2").PivotFields("VENDOR")
            .Orientation = xlRowField
            .Position = 1
        End With
        .PivotTables("PivotTable2").AddDataField .PivotTables( _
            "PivotTable2").PivotFields("BOOKED"), "Sum of BOOKED", xlSum
    End With
    ThisWorkbook.ShowPivotTableFieldList = False


End Sub