Skip to content

Instantly share code, notes, and snippets.

@imamuddinwp

imamuddinwp/BI_Report

Created July 13, 2021 06:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save imamuddinwp/7adaf73ae678653f26d90ef6beb20625 to your computer and use it in GitHub Desktop.
Save imamuddinwp/7adaf73ae678653f26d90ef6beb20625 to your computer and use it in GitHub Desktop.
Download ZIP
BI_Report
Raw
BI_Report
Sub BI_Report()
'
' Macro4 Macro
'
Selection.CurrentRegion.Select
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
Columns("A:J").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("D:J").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Cut
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Select
ActiveCell.FormulaR1C1 = "Cut"
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1").Select
ActiveCell.FormulaR1C1 = "Dif"
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").Select
ActiveCell.FormulaR1C1 = "Prdn"
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J1").Select
ActiveCell.FormulaR1C1 = "Dif"
Columns("A:A").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
'-------------------------------------------------
Range("D1").Select
Selection.AutoFilter
ActiveSheet.Range("D2").CurrentRegion.AutoFilter Field:=4, Criteria1:="<2", _
Operator:=xlAnd
'------------------------
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
ActiveSheet.ShowAllData
'---------------------------
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1").Select
ActiveCell.FormulaR1C1 = "Plan-Id"
Range("D2").Select
'-------------------------
ActiveWorkbook.SaveAs Filename:="D:\DataBackup\New folder\BI_Report.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'---------------------
Workbooks.Open Filename:="D:\DataBackup\New folder\Daily-Production.xlsm"
Sheets("PO-Color").Select
'-------------------------
Columns("D:D").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Windows("BI_Report.xlsx").Activate
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(C[-1],'[Daily-Production.xlsm]PO-Color'!C3:C4,2,0),0)"
'---------------------------------------------------------------------------
Selection.AutoFill Destination:=Range("D2:D" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'---------------------------------------------------
Range("G2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(C[-3],'[Daily-Production.xlsm]Cut-Prdn'!C7:C8,2,0),0)"
'---
Selection.AutoFill Destination:=Range("G2:G" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'-------------------------------------------------------
Range("J2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(C[-6],'[Daily-Production.xlsm]Cut-Prdn'!C7:C9,3,0),0)"
'---
Selection.AutoFill Destination:=Range("J2:J" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'-------------------------------------------------------
Range("H2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.AutoFill Destination:=Range("H2:H" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
'-----------------------------------------------------
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.AutoFill Destination:=Range("K2:K" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
'----------------------------------------------------
Windows("Daily-Production.xlsm").Activate
ActiveWorkbook.Close SaveChanges:=False
'----------------------------------------
Windows("BI_Report.xlsx").Activate
Application.CutCopyMode = False
ActiveWorkbook.Save
Range("H2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.AutoFill Destination:=Range("H2:H" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
'------------------------
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.AutoFill Destination:=Range("K2:K" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
'----------------------------
Range("E2").Select
ActiveWindow.FreezePanes = True
'--------------------
Range("A1").Select
ActiveCell.FormulaR1C1 = "Style#"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Or Qty"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Material"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Sewing"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Packing"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Shipment"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("B5").Select
Columns("B:B").ColumnWidth = 25.86
'-
Range("H:H,K:K").Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
'-
Range("E2").Select
'-
Sheets("Download-PO_Detail_Crosstable_d").Copy After:=Sheets(1)
Sheets("Download-PO_Detail_Crosstable_d").Select
Sheets("Download-PO_Detail_Crosstable_d").Copy After:=Sheets(2)
Sheets("Download-PO_Detail_Crosstab (2)").Select
Sheets("Download-PO_Detail_Crosstab (2)").Name = "Cut"
Sheets("Download-PO_Detail_Crosstab (3)").Select
Sheets("Download-PO_Detail_Crosstab (3)").Name = "Prdn"
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Download-PO_Detail_Crosstable_d").Select
'--------
'-
Sheets("Cut").Select
Selection.CurrentRegion.AutoFilter Field:=8, Criteria1:="=0", _
Operator:=xlAnd
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
ActiveSheet.ShowAllData
'----
Sheets("Prdn").Select
Selection.CurrentRegion.AutoFilter Field:=11, Criteria1:="=0", _
Operator:=xlAnd
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
ActiveSheet.ShowAllData
ActiveWorkbook.Save
'------------
Dim strFile As String: strFile = "C:\Users\m.imam\Downloads\Download-PO_Detail_Crosstable_data.csv"
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