Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
Below is the VBA code to create Pivot Charts for the mentioned questions and some additional tasks. Please insert this code into the VBA editor of your Excel workbook:
Sub CreateDashboard()
' Clear existing Pivot Tables and Charts (if any)
On Error Resume Next
ThisWorkbook.Sheets("Dashboard").PivotTables.Delete
ThisWorkbook.Sheets("Dashboard").ChartObjects.Delete
On Error GoTo 0
' Create Pivot Tables and Pivot Charts
Dim wsData As Worksheet
Dim wsDashboard As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache
' Set references to data and dashboard worksheets
Set wsData = ThisWorkbook.Sheets("Loan Applicant Data") ' Replace "Loan Applicant Data" with your data sheet name
Set wsDashboard = ThisWorkbook.Sheets("Dashboard")
' Create Gender Ratio Pivot Chart
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsData.UsedRange)
Set pt = pc.CreatePivotTable(TableDestination:=wsDashboard.Range("A1"), TableName:="Pivot_GenderRatio")
With pt
.PivotFields("Gender").Orientation = xlRowField
.AddDataField .PivotFields("ClientID"), "Count of Clients", xlCount
End With
Dim chartGenderRatio As ChartObject
Set chartGenderRatio = wsDashboard.ChartObjects.Add(Left:=200, Width:=300, Top:=20, Height:=250)
With chartGenderRatio.Chart
.SetSourceData Source:=pt.TableRange1
.ChartType = xlPie
.HasLegend = False
.HasTitle = True
.ChartTitle.Text = "Gender Ratio of Applicants"
End With
' Create Car Owner/Non-Owner Ratio Pivot Chart
' ... (similar to the above code, but with different fields)
' Create House Owner/Non-Owner Ratio Pivot Chart
' ... (similar to the above code, but with different fields)
' Continue creating Pivot Tables and Charts for other questions
' Add conditional formatting and freeze panes
' ... (code for conditional formatting and freezing panes)
End Sub
Sub ConvertToPDF()
' VBA Code to convert selected data into PDF from applicant data
Dim wsData As Worksheet
Set wsData = ThisWorkbook.Sheets("Loan Applicant Data") ' Replace "Loan Applicant Data" with your data sheet name
' Select the range you want to convert to PDF
' For example, to select all data in the "A1:G100" range, use the following line:
' wsData.Range("A1:G100").Select
' To convert the selection to PDF, use the following line:
' Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Path\To\Save\Your\File.pdf"
' Replace "C:\Path\To\Save\Your\File.pdf" with the desired file path to save the PDF
End Sub
Sub SpellCheckWorkbook()
' VBA Code to check spelling mistakes in the workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Cells.CheckSpelling
Next ws
End Sub
Before running the code, please make sure you have created a worksheet named "Loan Applicant Data" containing the relevant data, and another worksheet named "Dashboard" where the dashboard will be generated. Also, ensure you have set up the necessary tables and columns as described in the project requirements.
Please note that the above code serves as a starting point and may require modifications based on your specific data structure and requirements. Additionally, ensure you back up your data before running any VBA code to prevent accidental data loss.
For the parts of the project related to "Data Summary of All Female Clients" and "Data Summary of All Male Clients," the code will vary depending on the structure of your data. You may need to use Excel formulas or additional VBA code to achieve those specific data summaries.
Remember to enable macros in your Excel workbook to run the VBA code. You can do this by going to "File" > "Options" > "Trust Center" > "Trust Center Settings" > "Macro Settings" and choosing "Enable all macros" or "Enable all macros with notification" depending on your security preferences.