• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to Group with positive numbers in Pivot and create sheet with the same data.

Hi,

Would like to seek support to complete my VBA code to create my report. I have completed the data formatting and pivot table creation. Now I need to sort the Pivot table with values and create group for positive amounts (the data has both positive and negative values). Once the group is created, the same data (only positive values) needs to created as worksheet to prepare further reports. The positive amounts (can call it as receipts) needs to reported out with multiple tables.

I am stuck up with the Group option and then create sheet with the positive values. I have attached my macro with data (till pivot). Can anyone help?
 

Attachments

  • DebitBalanceReport.xlsm
    850.7 KB · Views: 4
If you are not getting any response, it's likely due to your question/workbook not having enough info.

I'd recommend manually creating mock up of your desired result from the sample. It isn't clear to me what your end result should look like at all.
 
Apoligizes if the requirement was not clear.

I created new sheet "Result of group" which I created after grouped all positive amounts. So I am looking to get the coding for the same.

Please let me know if it doesn't help.
 

Attachments

  • DebitBalanceReport.xlsm
    949.7 KB · Views: 7
First, I would suggest you start by cleaning up "Data" sheet.

You have currency column, which has numeric and text intermixed, this is not desirable for any sort of data analysis.

You are not really "Grouping" but you are just filtering out negative values from value field and then expanding out the pivot.

I'd recommend that you record filter operation and the expansion.
You should get something like below.
Code:
    ActiveSheet.PivotTables("DBPivotTable").PivotFields("ID Supplier").PivotFilters _
        .Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables( _
        "DBPivotTable").PivotFields("Sum of Amount in Eur"), Value1:=0
    Selection.ShowDetail = True

Then you can edit it like blow.

Code:
Sub Demo()
lRow As Long

With Sheets("PivotTable").PivotTables("DBPivotTable").PivotFields("ID Supplier")
    .ClearAllFilters
    .PivotFilters _
        .Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables( _
        "DBPivotTable").PivotFields("Sum of Amount in Eur"), Value1:=0
End With
lRow = Sheets("PivotTable").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("PivotTable").Range("B" & lRow).ShowDetail = True
End Sub
 
In your sheet Result of Group, on row 22 there is a negative number. It's not the only one. Is this intentional?
I suspect this is because that Supplier ID occurs several times in the source data, with both positive and negative amounts, but the total of all these amounts is positive.

If all you want is effectively a filtered list of the Data sheet where Amount in Eur >0 then this little macro will create a new sheet with that on it:
Code:
Sub blah()
With Sheets.Add(after:=Sheets(Sheets.Count))
  .Range("S1").Value = "Amount in Eur"
  .Range("S2").FormulaR1C1 = "="">0"""
  Sheets("Data").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("S1:S2"), CopyToRange:=.Cells(1), Unique:=False
  .Range("S1:S2").Clear
  '.ListObjects.Add xlSrcRange, .Range("A1").CurrentRegion, , xlYes 'uncomment this line if you want the result converted to an Excel table.
End With
End Sub
 
Last edited:
First, I would suggest you start by cleaning up "Data" sheet.

You have currency column, which has numeric and text intermixed, this is not desirable for any sort of data analysis.

You are not really "Grouping" but you are just filtering out negative values from value field and then expanding out the pivot.

I'd recommend that you record filter operation and the expansion.
You should get something like below.
Code:
    ActiveSheet.PivotTables("DBPivotTable").PivotFields("ID Supplier").PivotFilters _
        .Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables( _
        "DBPivotTable").PivotFields("Sum of Amount in Eur"), Value1:=0
    Selection.ShowDetail = True

Then you can edit it like blow.

Code:
Sub Demo()
lRow As Long

With Sheets("PivotTable").PivotTables("DBPivotTable").PivotFields("ID Supplier")
    .ClearAllFilters
    .PivotFilters _
        .Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables( _
        "DBPivotTable").PivotFields("Sum of Amount in Eur"), Value1:=0
End With
lRow = Sheets("PivotTable").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("PivotTable").Range("B" & lRow).ShowDetail = True
End Sub

Wow! It works with the filter.
lRow As Long - shows the error "statement invalid outside type block". When I ran the code without the statement it worked. is it fine?
The option to move only positive values (filtered data) does not seem to be correct as it shows the entire data in the Data sheet instead of filtered data.
 
Last edited:
Woops, didn't check the result fully, just the first few lines.

Easiest method is to add helper column to source table. Or use OLAP based pivot.
 
So, add helper column to source table that identify positive/negative value. Use that as pivot filter. Then the .ShowDetail method will only export visible items.

With value filter, it looks like even filtered out items are expanded in .ShowDetail.

But then, you wouldn't need pivot table for this process ;)
 
With value filter, it looks like even filtered out items are expanded in .ShowDetail.
I think this is what I was trying to point out in the first line of my message at msg#7, but the OP has chosen to ignore it. The clue is in the name of the field: Sum of…
 
Back
Top