Pivot Table VB Code Simplify

A

ateebali

Board Regular
Joined
Dec 13, 2018
Messages
108
Cross post
https://chandoo.org/forum/threads/p...-fields-and-dynamic-print-area-autoset.40764/

Can someone please help to simplify following vb Code
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Simplify in what way, and why (assuming it works)?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro for
Replies
2
Views
96
Sanderb
S
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
193
darmst24
D
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
157
Jette3173
J
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
176
Anthony47
A
C
  • Question Question
Pivot Table VBA
Replies
0
Views
230
chaddres
C

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top