How to use VBA to implement Pivot tables and match it with a column value from another worksheet

Question

What I am trying to do is stupidly simple when I do it manually but I am trying to automate it. I am not an expert so I am trying to get my logic right first and then I can write building blocks of code to achieve that.

What I have

The below is what I have so far.

Option Explicit

Sub ReportGeneration()

'Declarations

Dim InputSheet As Workbook, AttendanceDiscrepencyReporter As Workbook
Dim Start As Date
Dim Last As Date
Dim StartFormated As String
Dim LastFormated As String
Dim InputFileName As String
Dim ADRFileName As String
Dim TWorkingDays As Integer
Dim EmpEmail As Range
Dim EmpID As Range
Dim TLeave As Range
Dim TFlexi As Range
Dim TAttendance As Range
Dim IsAtOnsite As Range
Dim CMEmail As Range

'Create the Report Workbook

Start = Range("F7").Value
Last = Range("F8").Value
StartFormated = Replace((Range("F7").Value), "/", "_")
LastFormated = Replace((Range("F8").Value), "/", "_")
TWorkingDays = Int(Range("F10").Value)

Set AttendanceDiscrepencyReporter = Workbooks.Add
ADRFileName = ThisWorkbook.Path & "\" & "DiscrepencyReport_from_" & StartFormated & "_to_" & LastFormated & ".xlsx"

With AttendanceDiscrepencyReporter
    .Title = "Discrepency Report"
    .Subject = "Discrepency Report"
    .Sheets("Sheet1").Name = "Dashboard"
    .SaveAs Filename:=ADRFileName
    .Close
End With


'Open Input Workbook

InputFileName = ThisWorkbook.Path & "\" & "Master.xlsx"
Set InputSheet = Workbooks.Open(InputFileName, True, True)
Set AttendanceDiscrepencyReporter = Workbooks.Open(ADRFileName, True, False)

' Construct the Report Worksheet

'Email ID copying
InputSheet.Sheets("Base").Range("A1:A1000").Copy Destination:=AttendanceDiscrepencyReporter.Sheets("Dashboard").Range("A1")

'Employee ID copying
InputSheet.Sheets("Base").Range("B1:B1000").Copy Destination:=AttendanceDiscrepencyReporter.Sheets("Dashboard").Range("B1")

'Calculating Leaves and writing it into Report
Dim LastRow As Long
Dim LastCol As Long

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

' Close Workbooks

InputSheet.Close
AttendanceDiscrepencyReporter.Save
AttendanceDiscrepencyReporter.Close

End Sub

What it does so far

1. Reads the InputSheet Workbook's "Base" worksheet, copies the A and B column, pastes it into AttendanceDiscrepencyReporter workbook's Dashboard's worksheet.

What I want it to do next

1. Read the InputSheet Workbook's "Leave" Worksheet
2. I have an xlsm where I am triggering this macro from. That xlsm has Start and End date fields. I am storing those in the Start and Last variables as date.

My logic is

1. Declare TLeaves variable as integer and initialize to Zero
2. If the Start date in "Leave" worksheet is >= Start variable &&
3. If the End date in "Leave" worksheet is <= Last variable THEN
4. Loop through AttendanceDiscrepencyReporter 's "Dashboard" worksheet's Column A (it has the unique occurrance of all Email IDs) and compare it with "Leave" worksheet's Column G (that has email IDs too but not unique. For every leave the employee has taken, it will have a row). If a Match found, increment TLeaves by 1.
5. It also needs to categorize the Leave Type (Column F) - This is where my brain stops comprehending. It would be easy for me to do a Pivot manually by Employee Email ID and Leave Type. I am visualize this in VBA.
6. By end of loop for first email Id, finally write the TLeave value in "Dahboard" sheet's Column C. I am having struggle in how to identify in the loop which row this value will go to.

I am trying to google vba loop syntax and learn but if someone can give me a head-start with how to do those 5 steps then it will be helpful.

Here is screenshot of my Leave worksheet - https://pasteboard.co/HOTYAXE.png I am triggering my report from here - https://pasteboard.co/HOTZepE.png

I am also thinking of having integer variables for all the leave types separately then using just one TLeaves (Total Leaves). This way I can write individual totals in the destination sheet as Column c, d, e, etc.