Filling prepared form in excel with data in another sheet

cmike32 

It’s possible to automate the process of filling a form with data from another sheet in Excel using VBA. Here is how you can create multiple forms from a list of equipment, each on a new sheet:

Step-by-Step Guide

1. Prepare Your Data

1. Data Sheet:
• Name this sheet "EquipmentList".
• Assume the data starts from A1 and is structured as follows:

| A         | B          | C         |

|-----------|------------|-----------|

| Equipment | Model      | Serial No |

| Equip1    | Model1     | Serial1   |

| Equip2    | Model2     | Serial2   |

| ...       | ...        | ...       |

1. Form Template:
• Create a sheet named "FormTemplate".
• Design your form in this sheet. Assume you have placeholders for equipment data in specific cells, e.g.:
• Equipment: B2
• Model: B3
• Serial No: B4

1. Write VBA Code

1. Open the VBA Editor:
• Press Alt + F11 to open the VBA editor.
• Insert a new module by right-clicking on any existing module or sheet and choosing Insert > Module.
2. Paste the VBA Code:
• Copy and paste the following VBA code into the new module:

Vba Code is untested backup your file

Sub GenerateForms()
    Dim wsData As Worksheet
    Dim wsTemplate As Worksheet
    Dim newSheet As Worksheet
    Dim i As Long
    Dim lastRow As Long
    Dim newSheetName As String

    ' Set references to the data sheet and the template sheet
    Set wsData = ThisWorkbook.Sheets("EquipmentList")
    Set wsTemplate = ThisWorkbook.Sheets("FormTemplate")

    ' Find the last row with data in the data sheet
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    ' Loop through each row in the data sheet
    For i = 2 To lastRow ' Assuming the first row is headers
        ' Copy the template sheet to a new sheet
        wsTemplate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Set newSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

        ' Name the new sheet based on equipment name
        newSheetName = wsData.Cells(i, 1).Value
        newSheet.Name = newSheetName

        ' Fill the new sheet with data from the data sheet
        newSheet.Range("B2").Value = wsData.Cells(i, 1).Value ' Equipment
        newSheet.Range("B3").Value = wsData.Cells(i, 2).Value ' Model
        newSheet.Range("B4").Value = wsData.Cells(i, 3).Value ' Serial No
    Next i

    MsgBox "Forms generated successfully!", vbInformation
End Sub

 Customize the Code:

• Make sure the cell references in the code (B2, B3, B4) match the locations of the placeholders in your "FormTemplate".
• Ensure that "EquipmentList" and "FormTemplate" match the actual names of your sheets.

1. Run the Macro

1. Save your workbook as a macro-enabled workbook (.xlsm).
2. Run the Macro:
• Press Alt + F8 to open the macro dialog box.
• Select GenerateForms and click Run.

This will create a new sheet for each piece of equipment in your list, filling in the form with the corresponding data.

Example

EquipmentList Sheet:

| A         | B          | C         |

|-----------|------------|-----------|

| Equipment | Model      | Serial No |

| Equip1    | Model1     | Serial1   |

| Equip2    | Model2     | Serial2   |

| ...       | ...        | ...       |

FormTemplate Sheet:

| A        | B          |

|----------|------------|

| Equipment| (placeholder) |

| Model    | (placeholder) |

| Serial No| (placeholder) |

Running the macro will create sheets named Equip1, Equip2, etc., with their respective data filled in the placeholders.

This process automates the creation of forms, saving you significant time and effort. If you have any further customizations or requirements, please let me know!

The text, formulas and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.