Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
https://drive.google.com/file/d/1gDCF5I8rc5ZrQm5K9-aCSPqz1NBOpmCP/view?usp=sharing
VBA Code for PDF :
Sub PrintSelectionToPDF()
'SUBROUTINE: PrintSelectionToPDF
'DESCRIPTION: Print your currently selected range to a PDF
Dim rng As Range
Dim strfile As String
Dim myfile As Variant
If Selection.Count = 1 Then
Set rng = Application.InputBox("Select a range", "Get Range", Type:=8)
Else
Set rng = Selection
End If
'Prompt for save location
strfile = "Selection" & "_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"
strfile = ThisWorkbook.Path & "\" & strfile
myfile = Application.GetSaveAsFilename _
(InitialFileName:=strfile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")
If myfile <> "False" Then 'save as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If
End Sub
VBA Code for Highlighting Misspelled words:
Sub HighlightMisspellings()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
End If
Next cell
End Sub
Macro Code for Conditional Formatting of data and Freezing Panes :
Sub Macro_All()
'
' Macro_All Macro
' Conditional Formatting and Freezing panes
'
' Keyboard Shortcut: Ctrl+k
'
Range("Table1[CODE_GENDER]").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""M"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""F"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("E:E").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Y", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("F:F").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Y", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16751204
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("G:G").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("Table1[CNT_CHILDREN]").Select
Selection.FormatConditions.Delete
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 76
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 83
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 89
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 115
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 115
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 109
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 102
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 89
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 83
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 76
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Columns("H:H").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
Range("Table1[AMT_CREDIT]").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl5CRV)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 20
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 40
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(4)
.Type = xlConditionValuePercent
.Value = 60
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(5)
.Type = xlConditionValuePercent
.Value = 80
.Operator = 7
End With
Range("Table1[[#Headers],[AMT_ANNUITY]]").Select
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.FreezePanes = False
Range("Table1[[#Headers],[SK_ID_CURR]]").Select
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 7
Range("A2").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 12467
ActiveWindow.ScrollRow = 12929
ActiveWindow.ScrollRow = 14775
ActiveWindow.ScrollRow = 18469
ActiveWindow.ScrollRow = 19392
ActiveWindow.ScrollRow = 22162
ActiveWindow.ScrollRow = 22624
ActiveWindow.ScrollRow = 23548
ActiveWindow.ScrollRow = 24471
ActiveWindow.ScrollRow = 26318
ActiveWindow.ScrollRow = 26779
ActiveWindow.ScrollRow = 27703
ActiveWindow.ScrollRow = 27241
ActiveWindow.ScrollRow = 12005
ActiveWindow.ScrollRow = 6003
ActiveWindow.ScrollRow = 5542
ActiveWindow.ScrollRow = 4618
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 1848
ActiveWindow.ScrollRow = 4618
ActiveWindow.ScrollRow = 5080
ActiveWindow.ScrollRow = 6465
ActiveWindow.ScrollRow = 6927
ActiveWindow.ScrollRow = 7388
ActiveWindow.ScrollRow = 8312
ActiveWindow.ScrollRow = 9235
ActiveWindow.ScrollRow = 9697
ActiveWindow.ScrollRow = 8774
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B2").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 7850
ActiveWindow.ScrollRow = 9235
ActiveWindow.ScrollRow = 10620
ActiveWindow.ScrollRow = 12929
ActiveWindow.ScrollRow = 15237
ActiveWindow.ScrollRow = 16161
ActiveWindow.ScrollRow = 19392
ActiveWindow.ScrollRow = 19854
ActiveWindow.ScrollRow = 22162
ActiveWindow.ScrollRow = 23086
ActiveWindow.ScrollRow = 24933
ActiveWindow.ScrollRow = 25394
ActiveWindow.ScrollRow = 26318
ActiveWindow.ScrollRow = 27241
ActiveWindow.ScrollRow = 28164
ActiveWindow.ScrollRow = 29088
ActiveWindow.ScrollRow = 29549
ActiveWindow.ScrollRow = 30473
ActiveWindow.ScrollRow = 30934
ActiveWindow.ScrollRow = 31858
ActiveWindow.ScrollRow = 31396
ActiveWindow.ScrollRow = 18007
ActiveWindow.ScrollRow = 8774
ActiveWindow.ScrollRow = 5542
ActiveWindow.ScrollRow = 2
End Sub
Worked on MS Office Excel 2016 So unable to use New Functions (MS Excel 365) for Female Client's (Kindly Consider this)