Question about an Excel form

Question

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

hansvogelaar · Answer

Marinka&nbsp;
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 &gt; 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" &amp; NameRow).Value
        ' Print the form sheet
        FormSheet.PrintOut
    Next NameRow
    Application.ScreenUpdating = True
End Sub

marinka · Answer

Thank 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?

hansvogelaar · Answer

Marinka&nbsp;
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:
&nbsp;
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" &amp; NameRow).Value
        ' Print the form sheet
        FormSheet.PrintOut
    Next NameRow
    Application.ScreenUpdating = True
End Sub
&nbsp;

Forum Discussion

Question about an Excel form

Share

Resources