How to the office report form excel data that will be automatically filled up by macro.

See attached file. To generate leave forms, select "Yes" in the column "Create Leave Form?" then click the button. Hope that's what you were after - for completeness, the code is below as well.

Option Explicit
Option Base 1

Const conIDCol = "A"
Const conNameCol = "B"
Const conTypeCol = "C"
Const conDeptCol = "D"
Const conFromCol = "E"
Const conToCol = "F"
Const conDaysCol = "G"
Const conYNCol = "H"

Private Sub cmdCreateLeaveForm_Click()
Dim lngLastRow As Long, i As Long
Dim wksData As Worksheet, wksForm As Worksheet
Dim rng As Range, cel As Range
Dim strID As String, strName As String, strType As String, strDept As String, strFrom As String, strTo As String, strDays As String

    Set wksData = ThisWorkbook.Worksheets("LeaveData")
    Set wksForm = ThisWorkbook.Worksheets("FormTemplate")
    
    lngLastRow = wksData.Range(conYNCol & Rows.Count).End(xlUp).Row
    Set rng = wksData.Range(conYNCol & 2 & ":" & conYNCol & lngLastRow)
    For Each cel In rng
        If Left(cel.Value, 1) = "Y" Then
            i = i + 1
            strID = cel.Offset(0, -7).Text
            strName = cel.Offset(0, -6).Text
            strType = cel.Offset(0, -5).Text
            strDept = cel.Offset(0, -4).Text
            strFrom = Format(cel.Offset(0, -3).Text, "dd mmm yy")
            strTo = Format(cel.Offset(0, -2).Text, "dd mmm yy")
            strDays = cel.Offset(0, -1).Text
            
            'create instance of the leave form:
            wksForm.Copy after:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = strName & " " & strFrom & "-" & strTo
            With ThisWorkbook.Worksheets(strName & " " & strFrom & "-" & strTo)
                'populate fields in leave application form:
                .Range("FORMCodeNo").Value = strID
                .Range("FORMName").Value = strName
                .Range("FORMDepartment").Value = strDept
                .Range("FORMNature").Value = strType
                .Range("FORMDateFrom").Value = strFrom
                .Range("FORMDateTo").Value = strTo
                .Range("FORMTotalDays").Value = strDays
                .Range("FORMApplicationDate").Value = Format(Date, "dd mmm yy")
                'populate fields in leave pass:
                .Range("FORM_LP_CodeNo").Value = strID
                .Range("FORM_LP_Name").Value = strName
                .Range("FORM_LP_Department").Value = strDept
                .Range("FORM_LP_ApplicationDate").Value = .Range("FORMApplicationDate").Value
                .Range("FORM_LP_TotalDays").Value = .Range("FORMTotalDays").Value
                .Range("FORM_LP_DateFrom").Value = .Range("FORMDateFrom").Value
                .Range("FORM_LP_DateTo").Value = .Range("FORMDateTo").Value
            End With
        End If
    Next cel

    MsgBox ("Number of forms created = " & i)
    
End Sub