• 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.

Pivot Table not to show blank fields and dynamic print area autoset

A

Ateeb Ali

Member
Dear Sir
Example file attached, I need VB code any way out that pivot table only select the valid data and ignore blank cells, both can be seen in attached fie with different sheets.

Also I want Pivot table print area auto adjust and no need to do manually like if we remove data, it still shows two pages which I dont want and when increase data in pivot, it should add print pages likewise.

thanks in advance all ninjas

Regards
Ateeb Ali
 

Attachments

  • Chandoo.xlsb
    43.6 KB · Views: 1
Following code if someone can help to simplify

Code: 
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Report").Select
    Range("B9").Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Stitch Type").Orientation _
        = xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Operation Name"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("SPI").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("TOT Seam Length (Cm)"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Needle / Bobin"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("QUALITY").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("COUNT / PLY").Orientation _
        = xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Tex").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("COLOR").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("TOT MTR SINGLE GMT"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields( _
        "TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable4").PivotFields("With Extra %").Orientation _
        = xlHidden
    Range("B10").Select
    ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("TOT MTR WITH EXTRA %")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("TOT MTR WITH EXTRA %"), _
        "Count of TOT MTR WITH EXTRA %", xlCount
    With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
        "TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("TOT MTR SINGLE GMT")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields("TOT MTR SINGLE GMT"), "Count of TOT MTR SINGLE GMT" _
        , xlCount
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields( _
        "TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR"), _
        "Count of TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR", xlCount
    With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
        "Count of TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR")
        .Caption = _
        "Sum of TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
        "Count of TOT MTR SINGLE GMT")
        .Caption = "Sum of TOT MTR SINGLE GMT"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
        "Count of TOT MTR WITH EXTRA %")
        .Caption = "Sum of TOT MTR WITH EXTRA %"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Stitch Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Operation Name")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("SPI")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Seam Length (Cm)")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Seam Length (Cm)"). _
        Orientation = xlHidden
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("TOT Seam Length (Cm)")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Location")
        .Orientation = xlRowField
        .Position = 5
    End With
    Range("D7").Select
    ActiveWindow.SmallScroll Down:=0
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Location").Orientation = _
        xlHidden
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("QUALITY")
        .Orientation = xlRowField
        .Position = 5
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("COUNT / PLY")
        .Orientation = xlRowField
        .Position = 6
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Tex")
        .Orientation = xlRowField
        .Position = 7
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("COLOR")
        .Orientation = xlRowField
        .Position = 8
    End With
    Range("I7").Select
    Columns("I:I").ColumnWidth = 14.57
    Columns("J:J").ColumnWidth = 12.71
    Columns("K:K").ColumnWidth = 12
    Columns("K:K").ColumnWidth = 13.86
    Columns("I:I").ColumnWidth = 18.57
    Columns("I:I").ColumnWidth = 21.43
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A7:K10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit
    Range("I7").Select
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields( _
        "Sum of TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR"), _
        "Sum of TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR", xlSum
    ActiveSheet.PivotTables("PivotTable4").DataPivotField.PivotItems( _
        "Sum of TOT MTR SINGLE GMT").Caption = "TOT MTR"
    Range("J7").Select
    ActiveSheet.PivotTables("PivotTable4").DataPivotField.PivotItems( _
        "Sum of TOT MTR WITH FABRIC SHRINK MARGIN / GMT ASSORTMENT FACTOR").Caption = _
        "WITH FACTOR"
    Range("K7").Select
    ActiveSheet.PivotTables("PivotTable4").DataPivotField.PivotItems( _
        "Sum of TOT MTR WITH EXTRA %").Caption = "WITH EXTRA %"
    Range("K7").Select
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("J7").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A7:K100").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit
    Range("C8").Select
    ActiveSheet.PivotTables("PivotTable4").PivotSelect "'212'", xlDataAndLabel, _
        True
    Range("C11").Select
    ActiveWindow.SmallScroll Down:=-21
    Range("A7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Stitch Type")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("B7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Operation Name")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("C7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("SPI")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("D7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("TOT Seam Length (Cm)")
        .PivotItems("#VALUE!").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("E7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("QUALITY")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("F7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("COUNT / PLY")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("G7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Tex")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("H7").Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("COLOR")
        .PivotItems("0").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    Range("D7").Select
End Sub
 
It is solved with following code;
Code: 
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
   
    Application.ScreenUpdating = False
   
    For Each pt In ActiveSheet.PivotTables
        pt.ManualUpdate = True
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Visible = True
            Next pi
        Next pf
        pt.ManualUpdate = False
    Next pt
   
    Application.ScreenUpdating = True

With ActiveSheet.PivotTables("PivotTable4").PivotFields("Stitch Type")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("Operation Name")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("SPI")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("TOT Seam Length (Cm)")
        .PivotItems("#VALUE!").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("QUALITY")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
   
With ActiveSheet.PivotTables("PivotTable4").PivotFields("COUNT / PLY")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("Tex")
        .PivotItems("").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("COLOR")
        .PivotItems("0").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

End Sub
 
You must log in or register to reply here.
Back
Top