SOLVED

Filling prepared form in excel with data in another sheet

Copper Contributor

Hi everyone, 

I need to help with filling my form in one sheet with list of data in another sheet.

I have to create new forms of electrical equipment and each of that on new paper. I have list of equipment and I need create around 300 forms with different data. 

It is possible to does it in excel? 

 

I need to fill here:

Snímka obrazovky 2024-06-27 o 8.38.51.jpeg.png

 

From this:

IMG_2269.jpeg

 

 

3 Replies
best response confirmed by cmike32 (Copper Contributor)
Solution

@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.

@NikolinoDE  It’s work very well.

Many tanks to you.

My life is now easier

Dudemods @To fill a prepared form in Excel with data from another sheet, follow these steps for a streamlined process. First, ensure both the form and data sheets are organized clearly, with matching column headers where necessary. Begin by selecting the cell in the form where you want the data to appear. Use formulas like VLOOKUP or INDEX and MATCH to pull the relevant information from the data sheet.

1 best response

Accepted Solutions
best response confirmed by cmike32 (Copper Contributor)
Solution

@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.

View solution in original post