Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Finding Credit and Debit Data and Creating Pivot

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14

    Finding Credit and Debit Data and Creating Pivot

    Hi Friends,


    I have attached receivable statement. From that sheet I need to create two sheets once as Credit and debits.

    Sheet "Credit" contains negate transaction (In Column "H") and sheet "Debit" contains positive transactions.

    Vendor wise I need to create Pivot table for both sheet(Credit and Debit) as per the sheet attached.

    Kindly help to create VBA code for the same.

    Regards,

    Prabhu
    Attached Files Attached Files

  2. #2
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    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":

    Code:
    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
    Code:
    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
    Code:
    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
    Last edited by littleiitin; 01-23-2012 at 09:48 PM.

  3. #3
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14

    Finding Credit and Debit Data and Creating Pivote

    PFA
    Attached Files Attached Files

  4. #4
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Hi Friend,

    Thanks, But am getting error message as "Run-time error '9'

    "Subscript out of range"

    Debug highlight the below line.

    Plz help to resolve the same.

    Regards,

    Prabhu


    " ThisWorkbook.Worksheets("Credit").UsedRange.ClearC ontents"

  5. #5
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    Prabhu,
    I am seeing a space " ClearC ontents". Please remove it.
    I have tried in my workbook.. It is running fine at my end.

    Thanks
    Rahul Singh

  6. #6
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Hi Rahul,

    I Am using the same workbook which you have uploaded.

    I have inserted New date sheet and deleted the existing 3 sheets and run the macro.

    But the same error is repeating. I have attached the workbook for your reference.

    Regards,

    Prabhu
    Attached Files Attached Files

  7. #7
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Hi Rahul,

    Can you help to solve the Query?

    Regards,

    Prabhu

  8. #8
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Hi Friends,

    Can any help to fix this issue?

    Regards,

    Prabhu

  9. #9
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    Make sure to rename sheet1 to "Raw data" - also add code shown below - it will create the debit & credit sheets (if they dont already exists) - then the code runs fine - I have no idea if the data is right - but the code executes.

    Code:
    Sub CreatingPositiveNegativeSheet()
        
        Dim NewSheetX As Worksheet
        Astr = "NotExists"
        For ii = 1 To Worksheets.Count
            If Worksheets(ii).Name = "Credit" Then Astr = "Exists"
        Next ii
        If Astr = "NotExists" Then
            Set NewSheetX = Worksheets.Add    'This makes it active sheet
            ActiveSheet.Name = "Credit" ' Name of new 
        End If
        Astr = "NotExists"
        For ii = 1 To Worksheets.Count
            If Worksheets(ii).Name = "Debit" Then Astr = "Exists"
        Next ii
        If Astr = "NotExists" Then
            Set NewSheetX = Worksheets.Add    'This makes it active sheet
            ActiveSheet.Name = "Debit" ' Name of new sheet    
        End If
        ThisWorkbook.Worksheets("Raw Data").Activate 'This not need - but will make the Raw Data sheet the active sheet
    xl2007 - Windows 7
    xl hates the 255 number

  10. #10
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Hi,

    It is just creating two sheets. but data s are not moving from the data sheet(Raw data)

    Regards,

    Prabhu

Similar Threads

  1. finding the number of occurrence
    By zzzqinzzz in forum Excel Help
    Replies: 2
    Last Post: 12-13-2012, 10:24 AM
  2. Group Pivot Data Based On Row Values In One Column
    By mrmmickle1 in forum Excel Help
    Replies: 10
    Last Post: 10-09-2012, 11:46 PM
  3. Replies: 3
    Last Post: 05-14-2012, 11:30 AM
  4. Finding highest value in array
    By Rasm in forum Excel Help
    Replies: 15
    Last Post: 06-24-2011, 03:59 AM
  5. Finding Last Used Row or Column In Excel Sheet
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-14-2011, 03:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •