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

Hi Rinko. In the spreadsheet you have Staff ID, Staff Name, Leave Type, Leave Taken, Department, and the dates From & To, so 7 fields in total. In the leave form you want filled in there are other fields not listed in the spreadsheet - Code No. Designation, Address During Leave, and so on.

So while we can populate the fields from the spreadsheet into a new leave form (and auto-print it for you as well) ... it will still not have all the fields populated so it probably won't save that much time. Do you want to provide the other fields or just go with it as it is?

No problem - if this is now complete, please make the thread solved :-)

It's just the naming of the sheet ... it's limited to 31 characters. I've changed it so the forms are sequentially numbered instead. Replace the code with the below:

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 = "Leave Form " & i
            With ThisWorkbook.Worksheets("Leave Form " & i)
                '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

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

Hi MatrixMan,

Sorry for disturbing you. Though it is working nicely but facing "run-time error" 1004. This is happening when I am using actual name. like - Md. Obaidur Rahman Baig (23 Characters), this is the logest name amoung my team members. it only works upto 11 (Characters), like - Md. Obaidur. Is there any solution for that?

baig.JPG

Thank You.
Haniar.

You're welcome Rinko - thanks for the rep point and taking the time to say thanks

Cheers, MM