Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

FILE DRIVE link : https://docs.google.com/spreadsheets/d/1Y8qAImeznotlgNEr_8-FlbjswxmBLfdk/edit?usp=share_link&ouid=112616727609433107472&rtpof=true&sd=true

• Create a Macro for Data Formatting (Conditional Formatting, Freezing Panes) of workbook as same will be applied to the data of next quarter

Sub Macro1()
'
' Macro1 Macro
' conditional formatting and freezing panes
'

'
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:DT307512").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A1))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B2").Select
ActiveWindow.FreezePanes = True
End Sub

• Create VBA Code to convert selected data into pdf from applicant data

Sub ConvertToPDF()

Dim selectionRange As Range
Set selectionRange = Selection

With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save As PDF"
.Filters.Clear
.Filters.Add "PDF Files", "*.pdf"
.InitialFileName = "Applicant Data.pdf"
If .Show = -1 Then
Sheets(selectionRange.Parent.Name).Select
selectionRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.SelectedItems(1), Quality:=xlQualityStandard, IncludeDocProperties:=True
MsgBox "PDF file created successfully.", vbInformation, "PDF Created"
End If
End With

End Sub

• Check Spelling mistakes in the workbook using VBA Code

Sub CheckSpelling()

Dim currentSheet As Worksheet

For Each currentSheet In ActiveWorkbook.Worksheets
currentSheet.Activate
Cells.CheckSpelling SpellLang:=1033
Next currentSheet

MsgBox "Spelling check complete.", vbInformation, "Spelling Check"

End Sub