locked
Excel VBA: Format a specific Pivots column in multiple sheets RRS feed

  • Question

  • Hi 

    I am having difficulty just formatting a specific column called 'Numerator' in all workboods sheets; I can format all cells that meet the criteria , struggling to just do the one I want , any help appreciated 

    Issue

    All Columns called Numerator in the pivot in every sheet  with a value between 0 and 5 ,format to '<5'

    Problem Line: pvt.PivotFields("Numerator").PivotItems.DataRange.Cells

    Private Sub loopPivotTableAllSheet()

        Dim pvt As PivotTable
        Dim sh As Worksheet
        Dim c As Range
        'Loop through all the sheets in a workbook
        For Each sh In ThisWorkbook.Worksheets

            'If there is any pivot table on the sheet
            If sh.PivotTables.Count > 0 Then
        
                'Loop through all the pivots on the sheet
                For Each pvt In sh.PivotTables
                
                For Each c In pvt.PivotFields("Numerator").PivotItems.DataRange.Cells
                ''pvt.DataBodyRange.Cells
                 If c.Value >= 0 And c.Value < 5 Then
            
                 c.NumberFormat = "<5"
     
                
                End If
                Next
               
        Next pvt
                
            End If
        Next sh
        
    End Sub
    Wednesday, April 8, 2020 10:14 AM

All replies

  • To:  Carlo
    re:  PT code


    One thing I see:  the PivotItems object is a collection.
    You have to identify which member of the collection you are referring to.
    Such as. PivotItems(1)

    Also, DataBodyRange is a member of a PT not a PivotItem.

    And I doubt you can change the sign of a number using a number format of "<5"
    You can change a number to a negative value using:  Range("A1").Value2 = (VBA.Abs(Range("A1").Value2)) * -1

    '---



    The free Excel workbook "Professional_Compare" compares every cell Or
    each row in two worksheets - choice of compare type.
    Includes utilities:  "Clean Data, Quick Uniques".
    Download (no ads) from:  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents
    Wednesday, April 8, 2020 10:02 PM
  • Code works fine for all Pivots in all sheets , changes format to '<5' with a value between 0 and 5 

    I just want to do it for a Column called Numerator 

    Private Sub loopPivotTableAllSheet()

        Dim pvt As PivotTable
        Dim sh As Worksheet
        Dim c As Range
        'Loop through all the sheets in a workbook
        For Each sh In ThisWorkbook.Worksheets

            'If there is any pivot table on the sheet
            If sh.PivotTables.Count > 0 Then
        
                'Loop through all the pivots on the sheet
                For Each pvt In sh.PivotTables
                
                For Each c In  pvt.DataBodyRange.Cells
                 If c.Value >= 0 And c.Value < 5 Then
            
                 c.NumberFormat = "<5"
     
                
                End If
                Next
               
        Next pvt
                
            End If
        Next sh
        
    End Sub

    Wednesday, April 8, 2020 11:14 PM
  • Hi

    your format will show only <5 in the cells within 0-5. If that is you want then fine.

    you can refer below link for using advanced number formats.

    https://exceljet.net/custom-number-formats


    Best Regards, Asadulla Javed

    Monday, April 20, 2020 5:09 AM
    Answerer