[Solved] Create Multiple form from master Sheet

sharad_2307

New member
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,
 

Attachments

  • Form.xlsx
    14 KB · Views: 3
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.

Code:
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
 

Attachments

  • ExcelDemy.xlsm
    41.9 KB · Views: 3
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.
 
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.

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
 

Online statistics

Members online
0
Guests online
48
Total visitors
48

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top