[Solved] Create Multiple form from master Sheet

[sharad_2307]

HI Friends,

I have to create multiple form from master sheet , data higlighted in other work sheet will change (i.e name ,employee code department etc ) other part of form remain unchnaged. can we create for 200 or more employee?
it realy helpful anyone can help on this .

Thanks,

 

[shamimarita]

HI Friends,

I have to create multiple form from master sheet , data higlighted in other work sheet will change (i.e name ,employee code department etc ) other part of form remain unchnaged. can we create for 200 or more employee?
it realy helpful anyone can help on this .

Thanks,

Hello Sharad,

To generate the appraisal forms for 200 employees from the master sheet, you can use Excel's VBA to automate this task.

• Ensure your master sheet contains all necessary data fields for each employee. Based on the row of the Master sheet form will be created.
• Use one of the sheets as a template, like the one you've shown in the image.

I've attached the Excel file below.

Sub GenerateAppraisalForms()
    Dim wsMaster As Worksheet
    Dim wsTemplate As Worksheet
    Dim newWs As Worksheet
    Dim i As Integer
    Dim employeeName As String


    ' Set references to the master sheet and the template
    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsTemplate = ThisWorkbook.Sheets("Alka") 

    ' Loop through each employee in the master sheet
    For i = 2 To wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row
        ' Get employee name
        employeeName = wsMaster.Cells(i, 4).Value

        ' Create a new worksheet and name it
        Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        newWs.Name = employeeName

        ' Copy the template to the new sheet
        wsTemplate.Cells.Copy Destination:=newWs.Cells

        ' Fill in the data from the master sheet
        With newWs
            .Range("C2").Value = wsMaster.Cells(i, 4).Value ' Employee Name
            .Range("C3").Value = wsMaster.Cells(i, 5).Value ' Designation
            .Range("E3").Value = wsMaster.Cells(i, 6).Value ' Department
            .Range("C4").Value = wsMaster.Cells(i, 8).Value ' Reporting To
            .Range("E4").Value = wsMaster.Cells(i, 2).Value ' Location
            .Range("C5").Value = wsMaster.Cells(i, 7).Value ' Date of Joining
            .Range("E5").Value = wsMaster.Cells(i, 11).Value ' Yrs of Experience
            .Range("C6").Value = wsMaster.Cells(i, 3).Value ' Employee ID
            .Range("E6").Value = wsMaster.Cells(i, 12).Value ' Present CTC PA
        End With
    Next i
End Sub

 

[sharad_2307]

Hi Admin,

Thanks for help on my topics i have to two more dynamics in sheet one is excel form pagesetup is change after create new form for each employee, so pagesetup reamil same as per template , second thing is can we create employee form department wise & save to folder.

 

[shamimarita]

Hi Admin,

Thanks for help on my topics i have to two more dynamics in sheet one is excel form pagesetup is change after create new form for each employee, so pagesetup reamil same as per template , second thing is can we create employee form department wise & save to folder.

You are welcome. Please use the updated code to get your desired result. Also, make the changes based on the comments used in the code.

Sub GenerateAppraisalForms()
    Dim wsMaster As Worksheet
    Dim wsTemplate As Worksheet
    Dim newWs As Worksheet
    Dim i As Integer
    Dim employeeName As String
    Dim department As String
    Dim folderPath As String
    Dim deptFolderPath As String
    Dim fileName As String


    ' Set references to the master sheet and the template
    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsTemplate = ThisWorkbook.Sheets("Alka")


    ' Base folder path where the department folders will be created
    folderPath = "C:\EmployeeForms\" ' Change this to your desired path


    ' Loop through each employee in the master sheet
    For i = 2 To wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row
        ' Get employee name and department
        employeeName = wsMaster.Cells(i, 4).Value
        department = wsMaster.Cells(i, 6).Value


        ' Create a new worksheet and name it
        Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        newWs.Name = employeeName


        ' Copy the template to the new sheet
        wsTemplate.Cells.Copy Destination:=newWs.Cells


        ' Copy the PageSetup settings from the template to the new sheet
        With newWs.PageSetup
            .Orientation = wsTemplate.PageSetup.Orientation
            .PaperSize = wsTemplate.PageSetup.PaperSize
            .FitToPagesWide = wsTemplate.PageSetup.FitToPagesWide
            .FitToPagesTall = wsTemplate.PageSetup.FitToPagesTall
            .PrintArea = wsTemplate.PageSetup.PrintArea
            .LeftMargin = wsTemplate.PageSetup.LeftMargin
            .RightMargin = wsTemplate.PageSetup.RightMargin
            .TopMargin = wsTemplate.PageSetup.TopMargin
            .BottomMargin = wsTemplate.PageSetup.BottomMargin
            .HeaderMargin = wsTemplate.PageSetup.HeaderMargin
            .FooterMargin = wsTemplate.PageSetup.FooterMargin
            .CenterHorizontally = wsTemplate.PageSetup.CenterHorizontally
            .CenterVertically = wsTemplate.PageSetup.CenterVertically
        End With


        ' Fill in the data from the master sheet
        With newWs
            .Range("C2").Value = wsMaster.Cells(i, 4).Value ' Employee Name
            .Range("C3").Value = wsMaster.Cells(i, 5).Value ' Designation
            .Range("E3").Value = wsMaster.Cells(i, 6).Value ' Department
            .Range("C4").Value = wsMaster.Cells(i, 8).Value ' Reporting To
            .Range("E4").Value = wsMaster.Cells(i, 2).Value ' Location
            .Range("C5").Value = wsMaster.Cells(i, 7).Value ' Date of Joining
            .Range("E5").Value = wsMaster.Cells(i, 11).Value ' Yrs of Experience
            .Range("C6").Value = wsMaster.Cells(i, 3).Value ' Employee ID
            .Range("E6").Value = wsMaster.Cells(i, 12).Value ' Present CTC PA
        End With


        ' Create department folder if it doesn't exist
        deptFolderPath = folderPath & department & "\"
        If Dir(deptFolderPath, vbDirectory) = "" Then
            MkDir deptFolderPath
        End If


        ' Save the new sheet as a new workbook in the department folder
        fileName = deptFolderPath & employeeName & ".xlsx"
        newWs.Copy
        ActiveWorkbook.SaveAs fileName
        ActiveWorkbook.Close SaveChanges:=False


        ' Delete the new sheet from the current workbook
        Application.DisplayAlerts = False
        newWs.Delete
        Application.DisplayAlerts = True
    Next i
End Sub