Forum Discussion

Marinka's avatar
Marinka
Copper Contributor
Feb 21, 2024

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

  • Marinka 

    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
    • Marinka's avatar
      Marinka
      Copper Contributor
      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?
      • Marinka 

        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

         

Resources