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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
159
Anthony47
A
C
  • Question Question
Pivot Table VBA
Replies
0
Views
190
chaddres
C
Cubist
  • Solved
VBA - Need help optimize code that's updating pivot tables.
Replies
1
Views
266
Cubist
Cubist
S
  • Question Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
1K
RoryA
RoryA
J
  • Question Question
Macro for Changing Pivot Tables Dates
Replies
1
Views
870
Jar888
J

Forum statistics

Threads
1,222,752
Messages
6,168,007
Members
452,160
Latest member
Bekerinik

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