Forum Discussion
Marinka
Feb 21, 2024Copper Contributor
Question about an Excel form
I have a form that I print out monthly after I make date changes. I have to print this and other forms for multiple people. The form is the same, except for their name at the top. Right now I have the form laid out for each person and I make all the changes on each page and then print.
My question is: is there a way to make the changes on one page and print and it will change the name at the top automatically? Kind of like mail merge on Word, but this is in one cell on Excel.
Does that make sense? Thanks to anyone who can understand this and help me. 🙂
If you're using the desktop version of Excel for Mac or Windows, you can run a macro.
In the following, I'll make the following assumptions:
- The form is a worksheet named Form.
- The name is to be entered in cell B1 on this sheet.
- You have a separate worksheet in the same workbook named List, with a title such as Name in A1, and the names to be used in A2 and down.
To create the macro, follow these steps:
- Press Alt+F11 to activate the Visual Basic Editor.
- Select Insert > Module.
- Copy the code listed below into the module.
- Switch back to Excel.
- Save the workbook as a macro-enabled workbook (*.xlsm).
- You'll have to allow macros when you open the workbook.
- To run the macro, press Alt+F8 to activate the Macros dialog, select PrintForms and click Run.
The code:
Sub PrintForms() Dim FormSheet As Worksheet Dim ListSheet As Worksheet Dim NameRow As Long Dim LastRow As Long Application.ScreenUpdating = False ' The sheets to be used Set FormSheet = Worksheets("Form") Set ListSheet = Worksheets("List") ' The row of the last name LastRow = ListSheet.Range("A1").End(xlDown) ' Loop through the names For NameRow = 2 To LastRow ' Enter the next name in B1 on the form sheet FormSheet.Range("B1").Value = ListSheet.Range("A" & NameRow).Value ' Print the form sheet FormSheet.PrintOut Next NameRow Application.ScreenUpdating = True End Sub
- MarinkaCopper ContributorThank you!! Let me clarify some things.
The name is to be entered in A1 on the sheet.
I'm on a Mac Mini using Excel.
I don't have an ALT button.
Will this still work?Use Option instead of Alt:
Option+F11 to activate the Visual Basic Editor
Option+F8 to activate the Macros dialog.
Change the macro as follows:
Sub PrintForms() Dim FormSheet As Worksheet Dim ListSheet As Worksheet Dim NameRow As Long Dim LastRow As Long Application.ScreenUpdating = False ' The sheets to be used Set FormSheet = Worksheets("Form") ' change name as needed Set ListSheet = Worksheets("List") ' change name as needed ' The row of the last name LastRow = ListSheet.Range("A1").End(xlDown) ' Loop through the names For NameRow = 2 To LastRow ' Enter the next name in A1 on the form sheet FormSheet.Range("A1").Value = ListSheet.Range("A" & NameRow).Value ' Print the form sheet FormSheet.PrintOut Next NameRow Application.ScreenUpdating = True End Sub