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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
120
Sanderb
S
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
267
darmst24
D
S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
379
RoryA
RoryA
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
273
Jette3173
J
S
  • Question Question
Help with Pivot Table Macro
Replies
0
Views
64
SCPbrito
S

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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