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

Step 1: Data Preparation

2.Step 2: Create Pivot Tables

Sub CreateGenderRatioPivot()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets.Add

    ws.Name = "GenderRatio"

    ' Assuming your data is on a sheet named "Data"

    Dim dataSheet As Worksheet

    Set dataSheet = ThisWorkbook.Sheets("Data")

    ' Define the pivot table range

    Dim ptRange As Range

    Set ptRange = dataSheet.Range("A1").CurrentRegion

    ' Create the pivot table

    Dim pivotTable As PivotTable

    Set pivotTable = ws.PivotTableWizard(TableDestination:=ws.Cells(3, 1), _

        SourceType:=xlDatabase, SourceData:=ptRange)

    ' Add Gender to Rows and Count of Gender to Values

    With pivotTable.PivotFields("Gender")

        .Orientation = xlRowField

        .Position = 1

    End With

    pivotTable.AddDataField pivotTable.PivotFields("Gender"), "Count of Gender", xlCount

End Sub

2.Step 3: Create Pivot Charts

Sub CreateGenderRatioPivot()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets.Add

    ws.Name = "GenderRatio"

    ' Assuming your data is on a sheet named "Data"

    Dim dataSheet As Worksheet

    Set dataSheet = ThisWorkbook.Sheets("Data")

    ' Define the pivot table range

    Dim ptRange As Range

    Set ptRange = dataSheet.Range("A1").CurrentRegion

    ' Create the pivot table

    Dim pivotTable As PivotTable

    Set pivotTable = ws.PivotTableWizard(TableDestination:=ws.Cells(3, 1), _

        SourceType:=xlDatabase, SourceData:=ptRange)

    ' Add Gender to Rows and Count of Gender to Values

    With pivotTable.PivotFields("Gender")

        .Orientation = xlRowField

        .Position = 1

    End With

    pivotTable.AddDataField pivotTable.PivotFields("Gender"), "Count of Gender", xlCount

End Sub