Created
July 13, 2021 06:56
-
-
Save imamuddinwp/e3672e00f96d300c0ea59177482279d5 to your computer and use it in GitHub Desktop.
PO_Intregation
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 PO_Intregation() | |
' | |
' Macro1 Macro | |
' | |
' | |
Sheets("ExcelOut(PO)").Select | |
Rows("1:1").Select | |
Selection.Delete Shift:=xlUp | |
Cells.Select | |
ChDir "C:\Users\m.imam\Downloads" | |
ActiveWorkbook.SaveAs Filename:= _ | |
"C:\Users\m.imam\Downloads\FinishedGoodsOrderList.xlsx", FileFormat:= _ | |
xlOpenXMLWorkbook, CreateBackup:=False | |
ActiveWindow.Close | |
Workbooks.Open Filename:="C:\Users\m.imam\Downloads\FinishedGoodsOrderList.xlsx" | |
' --------------------------------------------------------------------------------------- | |
Columns("AV:AV").Select | |
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove | |
Range("AV1").Select | |
ActiveCell.FormulaR1C1 = "Color" | |
Range("AV2").Select | |
ActiveCell.FormulaR1C1 = "=RC[1]&""/""&RC[2]" | |
Range("AV2").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("AV2").Select | |
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ | |
:=False, Transpose:=False | |
Columns("AW:AX").Select | |
Selection.Delete Shift:=xlToLeft | |
Range("AV1").Select | |
Range("A1").Select | |
'---------------------------------------------------------------------------------------------- | |
Columns("BC:BC").Select | |
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove | |
Range("BC1").Select | |
ActiveCell.FormulaR1C1 = "Style Code" | |
Range("BC2").Select | |
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],8)" | |
Range("BC2").Select | |
''------------------- | |
Selection.AutoFill Destination:=Range("BC2:BC" & 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("BB:BB").Select | |
Application.CutCopyMode = False | |
Selection.Delete Shift:=xlToLeft | |
Range("A2").Select | |
'--------- | |
Cells.Select | |
Sheets.Add | |
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ | |
"ExcelOut(PO)!R1C1:R1048576C58", 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 | |
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("H3").Select | |
ActiveSheet.PivotTables("PivotTable1").PivotFields("Size").AutoSort _ | |
xlDescending, "Size" | |
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 | |
ActiveWorkbook.ShowPivotTableFieldList = False | |
Range("D5").Select | |
ActiveWindow.FreezePanes = True | |
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 | |
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.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 = 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.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 = 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.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 | |
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlDoNotRepeatLabels | |
'--------- | |
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Plan-ID'[All;Total]", _ | |
xlDataAndLabel, True | |
Selection.Font.Bold = True | |
Range("D5").Select | |
ActiveWorkbook.Save | |
'------------ | |
Dim strFile As String: strFile = "C:\Users\m.imam\Downloads\FinishedGoodsOrderList.xls" | |
If Len(Dir$(strFile)) > 0 Then Kill strFile | |
End Sub | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment