May 22 2023 12:52 AM
May 22 2023 12:52 AM
I need help. I have almost thousand Forms for Excel to create and upload to one drive. The questions for all the forms are the same. Only the name of the excel file it creates has to be of different names. I have a list of specific names. Is there shorter way to do this? Where I can just drop the list of names into an excel to create specific Forms for Excel. I have been manually keying in the same things for 100+ forms. My eyes hurt :crying_face:
May 22 2023 01:15 AM
To create multiple forms in Excel with different names but the same questions, maybe you can utilize Excel VBA (Visual Basic for Applications) to automate the process.
Here's an approach you can follow:
Sub CreateForms() Dim ws As Worksheet Dim lastRow As Long Dim i As Long ' Set the worksheet where the names are stored Set ws = ThisWorkbook.Worksheets("Sheet1") ' Update "Sheet1" to match your sheet name ' Find the last row in column A lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Loop through each name in the list For i = 2 To lastRow ' Get the name from column A Dim name As String name = ws.Cells(i, "A").Value ' Create a new workbook Dim newWorkbook As Workbook Set newWorkbook = Workbooks.Add ' Rename the workbook to the name from the list newWorkbook.SaveAs "C:\Path\To\Save\" & name & ".xlsx" ' Update the path and file extension as needed ' Add your form creation logic here ' You can create the form within the new workbook using VBA code ' Save and close the workbook newWorkbook.Close SaveChanges:=True Next i End Sub
Code is untested and made as far as I could understand from the text at hand.
3. Open your Excel file, press Alt + F11 to open the VBA editor, and insert a new module.
4. Copy and paste the above VBA code into the module.
5. Modify the code to match your specific requirements. Update the sheet name, column, path to save the files, and add your form creation logic within the loop.
6. Run the CreateForms macro by pressing F5 or Run in the VBA editor.
This VBA code will loop through each name in the list and create a new Excel workbook for each name. You can customize the form creation logic within the loop to suit your specific needs.
Remember to update the path to save the files and adjust any other parameters as necessary.
This is just one way to automate the creation of multiple Excel forms. You could also use other tools or programming languages to achieve the same result.