Created
July 13, 2021 06:58
-
-
Save imamuddinwp/f1d962e87ef327ee171ea2b8072fddda to your computer and use it in GitHub Desktop.
Trim_Arrangement
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Sub Trim_Arrangement() | |
| ' | |
| ' Macro1 Macro | |
| ' | |
| ' | |
| Sheets("ExcelOut(Trim)").Select | |
| Rows("1:1").Select | |
| Selection.Delete Shift:=xlUp | |
| '--------------------------------------------- | |
| Columns("AK:AK").Select | |
| Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove | |
| Range("AK1").Select | |
| ActiveCell.FormulaR1C1 = "Color" | |
| Range("AK2").Select | |
| ActiveCell.FormulaR1C1 = "=RC[1]&""/""&RC[2]" | |
| Range("AK2").Select | |
| ActiveCell.Offset(0, -1).Select | |
| Selection.End(xlDown).Select | |
| ActiveCell.Offset(0, 1).Select | |
| Range(Selection, Selection.End(xlUp)).Select | |
| Selection.FillDown | |
| Selection.Copy | |
| Range("AK2").Select | |
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ | |
| :=False, Transpose:=False | |
| Columns("AL:AM").Select | |
| Selection.Delete Shift:=xlToLeft | |
| Range("AK1").Select | |
| Range("A1").Select | |
| '---------------------------------------------------- | |
| Columns("AI:AI").Select | |
| Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove | |
| Range("AI1").Select | |
| ActiveCell.FormulaR1C1 = "Style Code" | |
| Range("AI2").Select | |
| ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],8)" | |
| Range("AI2").Select | |
| Selection.AutoFill Destination:=Range("AI2:AI" & Range("A" & Rows.Count).End(xlUp).Row) | |
| Range(Selection, Selection.End(xlDown)).Select | |
| Selection.Copy | |
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ | |
| :=False, Transpose:=False | |
| Columns("AH:AH").Select | |
| Application.CutCopyMode = False | |
| Selection.Delete Shift:=xlToLeft | |
| Range("A2").Select | |
| '--------------------------- | |
| Cells.Select | |
| Sheets.Add | |
| ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ | |
| "ExcelOut(Trim)!R1C1:R1048576C45", Version:=6).CreatePivotTable _ | |
| TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _ | |
| :=6 | |
| Sheets("Sheet1").Select | |
| Cells(3, 1).Select | |
| With ActiveSheet.PivotTables("PivotTable1") | |
| .ColumnGrand = True | |
| .HasAutoFormat = True | |
| .DisplayErrorString = False | |
| .DisplayNullString = True | |
| .EnableDrilldown = True | |
| .ErrorString = "" | |
| .MergeLabels = False | |
| .NullString = "" | |
| .PageFieldOrder = 2 | |
| .PageFieldWrapCount = 0 | |
| .PreserveFormatting = True | |
| .RowGrand = True | |
| .SaveData = True | |
| .PrintTitles = False | |
| .RepeatItemsOnEachPrintedPage = True | |
| .TotalsAnnotation = False | |
| .CompactRowIndent = 1 | |
| .InGridDropZones = True | |
| .DisplayFieldCaptions = True | |
| .DisplayMemberPropertyTooltips = False | |
| .DisplayContextTooltips = True | |
| .ShowDrillIndicators = True | |
| .PrintDrillIndicators = False | |
| .AllowMultipleFilters = False | |
| .SortUsingCustomLists = True | |
| .FieldListSortAscending = False | |
| .ShowValuesRow = False | |
| .CalculatedMembersInFilters = False | |
| .RowAxisLayout xlTabularRow | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotCache | |
| .RefreshOnFileOpen = False | |
| .MissingItemsLimit = xlMissingItemsDefault | |
| End With | |
| ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item") | |
| .Orientation = xlRowField | |
| .Position = 1 | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Style Code") | |
| .Orientation = xlRowField | |
| .Position = 2 | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Order Plan Number") | |
| .Orientation = xlRowField | |
| .Position = 3 | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Unit") | |
| .Orientation = xlRowField | |
| .Position = 4 | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Contracted ETD") | |
| .Orientation = xlRowField | |
| .Position = 5 | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA WH") | |
| .Orientation = xlRowField | |
| .Position = 6 | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Color") | |
| .Orientation = xlRowField | |
| .Position = 7 | |
| End With | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Size") | |
| .Orientation = xlColumnField | |
| .Position = 1 | |
| End With | |
| ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ | |
| "PivotTable1").PivotFields("Order Qty(pcs)"), "Sum of Order Qty(pcs)", xlSum | |
| ActiveWorkbook.ShowPivotTableFieldList = False | |
| Range("A4").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotFields("Item").Caption = _ | |
| "Style Name" | |
| Range("C4").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotFields("Order Plan Number"). _ | |
| Caption = "Plan-ID" | |
| Range("D4").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Unit").Caption = _ | |
| "BiZ" | |
| Range("E4").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotFields("Contracted ETD").Caption = _ | |
| "ETD" | |
| Range("F4").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotFields("ETA WH").Caption = "Wh" | |
| Range("G4").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True | |
| Selection.Columns.AutoFit | |
| Selection.Borders(xlDiagonalDown).LineStyle = xlNone | |
| Selection.Borders(xlDiagonalUp).LineStyle = xlNone | |
| With Selection.Borders(xlEdgeLeft) | |
| .LineStyle = xlContinuous | |
| .ColorIndex = 0 | |
| .TintAndShade = 0 | |
| .Weight = xlThin | |
| End With | |
| With Selection.Borders(xlEdgeTop) | |
| .LineStyle = xlContinuous | |
| .ColorIndex = 0 | |
| .TintAndShade = 0 | |
| .Weight = xlThin | |
| End With | |
| With Selection.Borders(xlEdgeBottom) | |
| .LineStyle = xlContinuous | |
| .ColorIndex = 0 | |
| .TintAndShade = 0 | |
| .Weight = xlThin | |
| End With | |
| With Selection.Borders(xlEdgeRight) | |
| .LineStyle = xlContinuous | |
| .ColorIndex = 0 | |
| .TintAndShade = 0 | |
| .Weight = xlThin | |
| End With | |
| With Selection.Borders(xlInsideVertical) | |
| .LineStyle = xlContinuous | |
| .ColorIndex = 0 | |
| .TintAndShade = 0 | |
| .Weight = xlThin | |
| End With | |
| With Selection.Borders(xlInsideHorizontal) | |
| .LineStyle = xlContinuous | |
| .ColorIndex = 0 | |
| .TintAndShade = 0 | |
| .Weight = xlThin | |
| End With | |
| Range("C4").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotFields("Plan-ID").Subtotals = Array _ | |
| (True, False, False, False, False, False, False, False, False, False, False, False) | |
| ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Plan-ID'[All;Total]", _ | |
| xlDataAndLabel, True | |
| With Selection.Interior | |
| .Pattern = xlSolid | |
| .PatternColorIndex = xlAutomatic | |
| .Color = 5296274 | |
| .TintAndShade = 0 | |
| .PatternTintAndShade = 0 | |
| End With | |
| Selection.Font.Bold = True | |
| Range("D5").Select | |
| ActiveWindow.FreezePanes = True | |
| With ActiveSheet.PivotTables("PivotTable1").PivotFields("Style Name") | |
| .PivotItems("(blank)").Visible = False | |
| End With | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .PrintTitleRows = "" | |
| .PrintTitleColumns = "" | |
| End With | |
| Application.PrintCommunication = True | |
| ActiveSheet.PageSetup.PrintArea = "" | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .LeftHeader = "" | |
| .CenterHeader = "" | |
| .RightHeader = "" | |
| .LeftFooter = "" | |
| .CenterFooter = "" | |
| .RightFooter = "" | |
| .LeftMargin = Application.InchesToPoints(0.7) | |
| .RightMargin = Application.InchesToPoints(0.7) | |
| .TopMargin = Application.InchesToPoints(0.75) | |
| .BottomMargin = Application.InchesToPoints(0.75) | |
| .HeaderMargin = Application.InchesToPoints(0.3) | |
| .FooterMargin = Application.InchesToPoints(0.3) | |
| .PrintHeadings = False | |
| .PrintGridlines = False | |
| .PrintComments = xlPrintNoComments | |
| .PrintQuality = 600 | |
| .CenterHorizontally = False | |
| .CenterVertically = False | |
| .Orientation = xlLandscape | |
| .Draft = False | |
| .PaperSize = xlPaperA4 | |
| .FirstPageNumber = xlAutomatic | |
| .Order = xlDownThenOver | |
| .BlackAndWhite = False | |
| .Zoom = 100 | |
| .PrintErrors = xlPrintErrorsDisplayed | |
| .OddAndEvenPagesHeaderFooter = False | |
| .DifferentFirstPageHeaderFooter = False | |
| .ScaleWithDocHeaderFooter = True | |
| .AlignMarginsHeaderFooter = True | |
| .EvenPage.LeftHeader.Text = "" | |
| .EvenPage.CenterHeader.Text = "" | |
| .EvenPage.RightHeader.Text = "" | |
| .EvenPage.LeftFooter.Text = "" | |
| .EvenPage.CenterFooter.Text = "" | |
| .EvenPage.RightFooter.Text = "" | |
| .FirstPage.LeftHeader.Text = "" | |
| .FirstPage.CenterHeader.Text = "" | |
| .FirstPage.RightHeader.Text = "" | |
| .FirstPage.LeftFooter.Text = "" | |
| .FirstPage.CenterFooter.Text = "" | |
| .FirstPage.RightFooter.Text = "" | |
| End With | |
| Application.PrintCommunication = True | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .PrintTitleRows = "" | |
| .PrintTitleColumns = "" | |
| End With | |
| Application.PrintCommunication = True | |
| ActiveSheet.PageSetup.PrintArea = "" | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .LeftHeader = "" | |
| .CenterHeader = "" | |
| .RightHeader = "" | |
| .LeftFooter = "" | |
| .CenterFooter = "" | |
| .RightFooter = "" | |
| .LeftMargin = Application.InchesToPoints(0.25) | |
| .RightMargin = Application.InchesToPoints(0.25) | |
| .TopMargin = Application.InchesToPoints(0.75) | |
| .BottomMargin = Application.InchesToPoints(0.75) | |
| .HeaderMargin = Application.InchesToPoints(0.3) | |
| .FooterMargin = Application.InchesToPoints(0.3) | |
| .PrintHeadings = False | |
| .PrintGridlines = False | |
| .PrintComments = xlPrintNoComments | |
| .PrintQuality = 600 | |
| .CenterHorizontally = False | |
| .CenterVertically = False | |
| .Orientation = xlLandscape | |
| .Draft = False | |
| .PaperSize = xlPaperA4 | |
| .FirstPageNumber = xlAutomatic | |
| .Order = xlDownThenOver | |
| .BlackAndWhite = False | |
| .Zoom = 100 | |
| .PrintErrors = xlPrintErrorsDisplayed | |
| .OddAndEvenPagesHeaderFooter = False | |
| .DifferentFirstPageHeaderFooter = False | |
| .ScaleWithDocHeaderFooter = True | |
| .AlignMarginsHeaderFooter = True | |
| .EvenPage.LeftHeader.Text = "" | |
| .EvenPage.CenterHeader.Text = "" | |
| .EvenPage.RightHeader.Text = "" | |
| .EvenPage.LeftFooter.Text = "" | |
| .EvenPage.CenterFooter.Text = "" | |
| .EvenPage.RightFooter.Text = "" | |
| .FirstPage.LeftHeader.Text = "" | |
| .FirstPage.CenterHeader.Text = "" | |
| .FirstPage.RightHeader.Text = "" | |
| .FirstPage.LeftFooter.Text = "" | |
| .FirstPage.CenterFooter.Text = "" | |
| .FirstPage.RightFooter.Text = "" | |
| End With | |
| Application.PrintCommunication = True | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .PrintTitleRows = "" | |
| .PrintTitleColumns = "" | |
| End With | |
| Application.PrintCommunication = True | |
| ActiveSheet.PageSetup.PrintArea = "" | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .LeftHeader = "" | |
| .CenterHeader = "" | |
| .RightHeader = "" | |
| .LeftFooter = "" | |
| .CenterFooter = "" | |
| .RightFooter = "" | |
| .LeftMargin = Application.InchesToPoints(0.25) | |
| .RightMargin = Application.InchesToPoints(0.25) | |
| .TopMargin = Application.InchesToPoints(0.75) | |
| .BottomMargin = Application.InchesToPoints(0.75) | |
| .HeaderMargin = Application.InchesToPoints(0.3) | |
| .FooterMargin = Application.InchesToPoints(0.3) | |
| .PrintHeadings = False | |
| .PrintGridlines = False | |
| .PrintComments = xlPrintNoComments | |
| .PrintQuality = 600 | |
| .CenterHorizontally = False | |
| .CenterVertically = False | |
| .Orientation = xlLandscape | |
| .Draft = False | |
| .PaperSize = xlPaperA4 | |
| .FirstPageNumber = xlAutomatic | |
| .Order = xlDownThenOver | |
| .BlackAndWhite = False | |
| .Zoom = False | |
| .FitToPagesWide = 1 | |
| .FitToPagesTall = 0 | |
| .PrintErrors = xlPrintErrorsDisplayed | |
| .OddAndEvenPagesHeaderFooter = False | |
| .DifferentFirstPageHeaderFooter = False | |
| .ScaleWithDocHeaderFooter = True | |
| .AlignMarginsHeaderFooter = True | |
| .EvenPage.LeftHeader.Text = "" | |
| .EvenPage.CenterHeader.Text = "" | |
| .EvenPage.RightHeader.Text = "" | |
| .EvenPage.LeftFooter.Text = "" | |
| .EvenPage.CenterFooter.Text = "" | |
| .EvenPage.RightFooter.Text = "" | |
| .FirstPage.LeftHeader.Text = "" | |
| .FirstPage.CenterHeader.Text = "" | |
| .FirstPage.RightHeader.Text = "" | |
| .FirstPage.LeftFooter.Text = "" | |
| .FirstPage.CenterFooter.Text = "" | |
| .FirstPage.RightFooter.Text = "" | |
| End With | |
| Application.PrintCommunication = True | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .PrintTitleRows = "" | |
| .PrintTitleColumns = "" | |
| End With | |
| Application.PrintCommunication = True | |
| ActiveSheet.PageSetup.PrintArea = "" | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .LeftHeader = "" | |
| .CenterHeader = "" | |
| .RightHeader = "" | |
| .LeftFooter = "" | |
| .CenterFooter = "" | |
| .RightFooter = "" | |
| .LeftMargin = Application.InchesToPoints(0.25) | |
| .RightMargin = Application.InchesToPoints(0.25) | |
| .TopMargin = Application.InchesToPoints(0.75) | |
| .BottomMargin = Application.InchesToPoints(0.75) | |
| .HeaderMargin = Application.InchesToPoints(0.3) | |
| .FooterMargin = Application.InchesToPoints(0.3) | |
| .PrintHeadings = False | |
| .PrintGridlines = False | |
| .PrintComments = xlPrintNoComments | |
| .PrintQuality = 600 | |
| .CenterHorizontally = True | |
| .CenterVertically = False | |
| .Orientation = xlLandscape | |
| .Draft = False | |
| .PaperSize = xlPaperA4 | |
| .FirstPageNumber = xlAutomatic | |
| .Order = xlDownThenOver | |
| .BlackAndWhite = False | |
| .Zoom = False | |
| .FitToPagesWide = 1 | |
| .FitToPagesTall = False | |
| .PrintErrors = xlPrintErrorsDisplayed | |
| .OddAndEvenPagesHeaderFooter = False | |
| .DifferentFirstPageHeaderFooter = False | |
| .ScaleWithDocHeaderFooter = True | |
| .AlignMarginsHeaderFooter = True | |
| .EvenPage.LeftHeader.Text = "" | |
| .EvenPage.CenterHeader.Text = "" | |
| .EvenPage.RightHeader.Text = "" | |
| .EvenPage.LeftFooter.Text = "" | |
| .EvenPage.CenterFooter.Text = "" | |
| .EvenPage.RightFooter.Text = "" | |
| .FirstPage.LeftHeader.Text = "" | |
| .FirstPage.CenterHeader.Text = "" | |
| .FirstPage.RightHeader.Text = "" | |
| .FirstPage.LeftFooter.Text = "" | |
| .FirstPage.CenterFooter.Text = "" | |
| .FirstPage.RightFooter.Text = "" | |
| End With | |
| Application.PrintCommunication = True | |
| Application.CutCopyMode = False | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .PrintTitleRows = "$4:$4" | |
| .PrintTitleColumns = "" | |
| End With | |
| Application.PrintCommunication = True | |
| ActiveSheet.PageSetup.PrintArea = "" | |
| Application.PrintCommunication = False | |
| With ActiveSheet.PageSetup | |
| .LeftHeader = "" | |
| .CenterHeader = "" | |
| .RightHeader = "" | |
| .LeftFooter = "" | |
| .CenterFooter = "" | |
| .RightFooter = "" | |
| .LeftMargin = Application.InchesToPoints(0.25) | |
| .RightMargin = Application.InchesToPoints(0.25) | |
| .TopMargin = Application.InchesToPoints(0.75) | |
| .BottomMargin = Application.InchesToPoints(0.75) | |
| .HeaderMargin = Application.InchesToPoints(0.3) | |
| .FooterMargin = Application.InchesToPoints(0.3) | |
| .PrintHeadings = False | |
| .PrintGridlines = False | |
| .PrintComments = xlPrintNoComments | |
| .PrintQuality = 600 | |
| .CenterHorizontally = True | |
| .CenterVertically = False | |
| .Orientation = xlLandscape | |
| .Draft = False | |
| .PaperSize = xlPaperA4 | |
| .FirstPageNumber = xlAutomatic | |
| .Order = xlDownThenOver | |
| .BlackAndWhite = False | |
| .Zoom = False | |
| .FitToPagesWide = 1 | |
| .FitToPagesTall = False | |
| .PrintErrors = xlPrintErrorsDisplayed | |
| .OddAndEvenPagesHeaderFooter = False | |
| .DifferentFirstPageHeaderFooter = False | |
| .ScaleWithDocHeaderFooter = True | |
| .AlignMarginsHeaderFooter = True | |
| .EvenPage.LeftHeader.Text = "" | |
| .EvenPage.CenterHeader.Text = "" | |
| .EvenPage.RightHeader.Text = "" | |
| .EvenPage.LeftFooter.Text = "" | |
| .EvenPage.CenterFooter.Text = "" | |
| .EvenPage.RightFooter.Text = "" | |
| .FirstPage.LeftHeader.Text = "" | |
| .FirstPage.CenterHeader.Text = "" | |
| .FirstPage.RightHeader.Text = "" | |
| .FirstPage.LeftFooter.Text = "" | |
| .FirstPage.CenterFooter.Text = "" | |
| .FirstPage.RightFooter.Text = "" | |
| End With | |
| Application.PrintCommunication = True | |
| '- | |
| Range("H3").Select | |
| ActiveSheet.PivotTables("PivotTable1").PivotFields("Size").AutoSort _ | |
| xlDescending, "Size" | |
| ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlDoNotRepeatLabels | |
| Range("D5").Select | |
| ActiveWorkbook.Save | |
| End Sub | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment