Solved Select name from list and print form letter

December 19, 2012 at 07:41:45
Specs: Windows 7
I have a form letter that inports data based on the employee's name I enter in cell A1. I need to insert the name and then print the schedule and then insert the next name on the list and print, etc. I have over 300 employees' schedules to print. I know I need a macro but I do not know enough to create one on my own. Any help would be greatly appreciated.

See More: Select name from list and print form letter

Report •


✔ Best Answer
December 20, 2012 at 15:15:17
Let's concentrate on the error first. 2 questions

Do you have a Spreadsheet named "List" where the names are stored?
If not you will need to either change the name of the spreadsheet that contains
the list to "List" or change the Macro from wherever "List" is mentioned to
your sheet name
.

Where does the Macro reside? Refer to Post #6

EDIT:
I see in a previous post that you do say there is a worksheet called List
so ignore that question.

I tested the macro in the Sheet1 module and got the same error message so I
am pretty confident that that is the problem.

Please verify that your macro is in Module1
(also make sure you delete the macro from the previous location if it was somewhere
else after you create the macro in Module1)

Another important thing to determine is where you located the Print button.
It must be in the Form worksheet.

I've added some stuff to the Macro

1. A pop up at the beginning asking if you want to run everything or just a test.
This is to decrease the possibility of running 300 or more wasted pages on the printer.

2. I've added a delay although I'm not certain its absolutely necessary.

 Sub RunAll_V2()
    
        NoRows = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row
        
 Resp = MsgBox("Do you want to run a sample? (If you choose NO it will run all " & NoRows & " pages)", vbYesNoCancel)
        
    If Resp = 7 Then
            PriNumber = NoRows
    ElseIf Resp = 6 Then
            PriNumber = 2
    Else
            Resp2 = MsgBox("Print cancelled", vbOKOnly)
            End
    End If

                
     For i = 1 To PriNumber
                RName = Sheets("List").Cells(i, 1)
                
                Range("A1") = RName
                
                'DO WHATEVER COMAND YOU DO TO RUN THE FORM LETTER
                
        Range("A1:N27").Select
            ActiveSheet.PageSetup.PrintArea = "$A$1:$N$27"
            With ActiveSheet.PageSetup
                .PrintTitleRows = ""
                .PrintTitleColumns = ""
            End With
            ActiveSheet.PageSetup.PrintArea = "$A$1:$N$27"
            With ActiveSheet.PageSetup
                .LeftHeader = ""
                .CenterHeader = ""
                .RightHeader = ""
                .LeftFooter = ""
                .CenterFooter = ""
                .RightFooter = ""
                .LeftMargin = Application.InchesToPoints(0.7)
                .RightMargin = Application.InchesToPoints(0.7)
                .TopMargin = Application.InchesToPoints(0.75)
                .BottomMargin = Application.InchesToPoints(0.75)
                .HeaderMargin = Application.InchesToPoints(0.3)
                .FooterMargin = Application.InchesToPoints(0.3)
                .PrintHeadings = False
                .PrintGridlines = False
                .PrintComments = xlPrintNoComments
                .PrintQuality = 600
                .CenterHorizontally = False
                .CenterVertically = False
                .Orientation = xlLandscape
                .Draft = False
                .PaperSize = xlPaperA4
                .FirstPageNumber = xlAutomatic
                .Order = xlDownThenOver
                .BlackAndWhite = False
                .Zoom = 100
                .PrintErrors = xlPrintErrorsDisplayed
                .OddAndEvenPagesHeaderFooter = False
                .DifferentFirstPageHeaderFooter = False
                .ScaleWithDocHeaderFooter = True
                .AlignMarginsHeaderFooter = True
                .EvenPage.LeftHeader.Text = ""
                .EvenPage.CenterHeader.Text = ""
                .EvenPage.RightHeader.Text = ""
                .EvenPage.LeftFooter.Text = ""
                .EvenPage.CenterFooter.Text = ""
                .EvenPage.RightFooter.Text = ""
                .FirstPage.LeftHeader.Text = ""
                .FirstPage.CenterHeader.Text = ""
                .FirstPage.RightHeader.Text = ""
                .FirstPage.LeftFooter.Text = ""
                .FirstPage.CenterFooter.Text = ""
                .FirstPage.RightFooter.Text = ""
            End With
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Range("A1").Select
    
        Application.Wait (Now() + TimeValue("00:00:02"))
        
        
        
        Next
        
        
    End Sub




#1
December 19, 2012 at 12:58:21
Hi Russell

Here is a start for you. It assumes that you have a sheet named "List" that has all 300 names listed in A1 to A300. If you need any more detailed help we would, of course, need equally detailed descriptions of your layouts and processes.

    Sub RunAll()
    
        NoRows = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row
        
        For i = 1 To NoRows
        
                RName = Sheets("List").Cells(i, 1)
                
                Range("A1") = RName
                
                'DO WHATEVER COMAND YOU DO TO RUN THE FORM LETTER
                
        Next
        
        
    End Sub


Report •

#2
December 19, 2012 at 15:39:37
Thank you for your quick reply. I have entered the macro and assigned it to a button. I do have the sheet (List) with all the names. When I try to run the macro, I get the following message: Microsoft Visual Basic - Run-time Error '1004': Application-defined or object-defined error.

Also, once the name is entered into "A1", it automatically populates the schedule so all I need it to do is print that person's schedule and then insert the next name, print, and so on.


Report •

#3
December 19, 2012 at 15:54:14
A few questions in return

1. What version of Excel are you using
2. Can you copy and paste a portion of your List sheet data so I can see what it looks like (you may want to substitute some fake names)
3. How is the schedule "automatically populated"?
4. Describe the steps you do to print the form


Report •

Related Solutions

#4
December 20, 2012 at 06:19:12
Good Morning AlteK,

I am using Excel 2007. Here's a sample of the "list":
Fred Smith
Barney Rubble
Wilma Flintstone
Pebbles Flintstone
Bill Jones
All the names on the real list are sorted by last name and are all in column A.

Here is a partial copy of the "schedule":

1. Fred Smith Cell Phone: _______________________________

2. 2013 Riverside County Date Festival
3. Monday 1/28/13 Tuesday 1/29/13 Wednesday 1/30/13
4. Start End Start End Start End

5.

The name is cell A1. The first week days are row 3. The Start/End times are row 4 and row 5 is where the actual scheduled start/end times are populated. The formula in each start/end time cell uses a VLOOKUP to compare the name in A1 to all the scheduled shifts on that date from the master schedule (a different workbook). The master schedule (depending on event) can be up to 650 rows on any given day. Once I enter the name in cell A1, it populates all the scheduled start/end times for the entire event (up to 7 weeks). I then just click File, Print. The print range is set for A1:N27. I would then enter the next name and repeat the process.

I hope this all makes sense. I really appreciate all your help.




Report •

#5
December 20, 2012 at 12:37:57
Hi Russell
That was a good explanation of what is happening in your application (and amazingly, based on your list of names, we have many acquaintances in common :) )

There are a couple of hurdles to overcome here.

1. the error message you are getting. I'm not sure why that is happening. I'll do a bit of research on that but I may need your help. Could you open the macro, ensure your spreadsheet is open with the forms sheet active, and step through the macro using F8 and find out where the error comes up.

2. The macro will run in an instant and there may be issues with sending 300 print commands to your printer in less than a second. But for testing purposes we should start with a small sample of the list. To do this, change the line

For i = 1 To NoRows

to

For i = 1 To 5

This will roll through the first 5 names on the list

3. To get it to print automatically I ran the macro recorder and then set the print range (A1 to N27), set the orientation to Landscape and clicked on the print and then stopped the recorder. The results are below but you may have to substitute all of that with your own recorded macro.

When I ran it (I was printing to a PDF) it worked - generating all the PDFs but I only had about 10 names in my list. there may be memory issues with 300 names so we may have to insert pauses in the macro (or some other device) to let the memory clear or your printer cope with the speed and volume of commands).

Here is the macro with my Print commands inserted.

 Sub RunAll()
    
        NoRows = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row
        
        For i = 1 To NoRows  'CHANGE THE "NoRows" to "5" (without quoted) for TESTING PURPOSES
        
                RName = Sheets("List").Cells(i, 1)
                
                Range("A1") = RName
                
                'HERE IS MY GENERATED CODE TO PRINT - 
                'YOU MAY NEED TO GENERATE YOUR OWN TO REPLACE THIS
                
        Range("A1:N27").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$N$27"
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$N$27"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Range("A1").Select
           
        
        Next
        
        
    End Sub


Report •

#6
December 20, 2012 at 14:17:15
One of the causes of the error is if you have placed the macro in a Spreadsheet module rather than an independent module. In the picture you will see what that looks like. If this is the case you'll need to create "Module1" by clicking Insert in the task bar at the top of the Visual Basic screen and then click on Module. If you' have already placed your macro in Module1 (or 2 or 3 etc) then we'll need to find another cause for the error.

AutoPrint01


Report •

#7
December 20, 2012 at 14:39:43
Thanks for sticking in there with me.

I made the change from NoRows to 5 and then stepped through the macro using F8. The Sub RunA11() was highlighted in yellow. The NoRows line highlighted in yellow. Then I hit F8 and get the Run-Time error message.

I didn't think about sending so much print data to the printer at once. Is there a way to build a small pause into the macro so after it selects the name and sends the print command, it delays for a second or two so I don't overload the printer?

Thanks.


Report •

#8
December 20, 2012 at 15:15:17
✔ Best Answer
Let's concentrate on the error first. 2 questions

Do you have a Spreadsheet named "List" where the names are stored?
If not you will need to either change the name of the spreadsheet that contains
the list to "List" or change the Macro from wherever "List" is mentioned to
your sheet name
.

Where does the Macro reside? Refer to Post #6

EDIT:
I see in a previous post that you do say there is a worksheet called List
so ignore that question.

I tested the macro in the Sheet1 module and got the same error message so I
am pretty confident that that is the problem.

Please verify that your macro is in Module1
(also make sure you delete the macro from the previous location if it was somewhere
else after you create the macro in Module1)

Another important thing to determine is where you located the Print button.
It must be in the Form worksheet.

I've added some stuff to the Macro

1. A pop up at the beginning asking if you want to run everything or just a test.
This is to decrease the possibility of running 300 or more wasted pages on the printer.

2. I've added a delay although I'm not certain its absolutely necessary.

 Sub RunAll_V2()
    
        NoRows = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row
        
 Resp = MsgBox("Do you want to run a sample? (If you choose NO it will run all " & NoRows & " pages)", vbYesNoCancel)
        
    If Resp = 7 Then
            PriNumber = NoRows
    ElseIf Resp = 6 Then
            PriNumber = 2
    Else
            Resp2 = MsgBox("Print cancelled", vbOKOnly)
            End
    End If

                
     For i = 1 To PriNumber
                RName = Sheets("List").Cells(i, 1)
                
                Range("A1") = RName
                
                'DO WHATEVER COMAND YOU DO TO RUN THE FORM LETTER
                
        Range("A1:N27").Select
            ActiveSheet.PageSetup.PrintArea = "$A$1:$N$27"
            With ActiveSheet.PageSetup
                .PrintTitleRows = ""
                .PrintTitleColumns = ""
            End With
            ActiveSheet.PageSetup.PrintArea = "$A$1:$N$27"
            With ActiveSheet.PageSetup
                .LeftHeader = ""
                .CenterHeader = ""
                .RightHeader = ""
                .LeftFooter = ""
                .CenterFooter = ""
                .RightFooter = ""
                .LeftMargin = Application.InchesToPoints(0.7)
                .RightMargin = Application.InchesToPoints(0.7)
                .TopMargin = Application.InchesToPoints(0.75)
                .BottomMargin = Application.InchesToPoints(0.75)
                .HeaderMargin = Application.InchesToPoints(0.3)
                .FooterMargin = Application.InchesToPoints(0.3)
                .PrintHeadings = False
                .PrintGridlines = False
                .PrintComments = xlPrintNoComments
                .PrintQuality = 600
                .CenterHorizontally = False
                .CenterVertically = False
                .Orientation = xlLandscape
                .Draft = False
                .PaperSize = xlPaperA4
                .FirstPageNumber = xlAutomatic
                .Order = xlDownThenOver
                .BlackAndWhite = False
                .Zoom = 100
                .PrintErrors = xlPrintErrorsDisplayed
                .OddAndEvenPagesHeaderFooter = False
                .DifferentFirstPageHeaderFooter = False
                .ScaleWithDocHeaderFooter = True
                .AlignMarginsHeaderFooter = True
                .EvenPage.LeftHeader.Text = ""
                .EvenPage.CenterHeader.Text = ""
                .EvenPage.RightHeader.Text = ""
                .EvenPage.LeftFooter.Text = ""
                .EvenPage.CenterFooter.Text = ""
                .EvenPage.RightFooter.Text = ""
                .FirstPage.LeftHeader.Text = ""
                .FirstPage.CenterHeader.Text = ""
                .FirstPage.RightHeader.Text = ""
                .FirstPage.LeftFooter.Text = ""
                .FirstPage.CenterFooter.Text = ""
                .FirstPage.RightFooter.Text = ""
            End With
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Range("A1").Select
    
        Application.Wait (Now() + TimeValue("00:00:02"))
        
        
        
        Next
        
        
    End Sub



Report •

#9
December 21, 2012 at 06:32:32
IT WORKS!!!

Since I have already set-up the print parameters, I changed the print command from what you had to: "ActiveWindow.SelectedSheets.PrintOut Copies:=1".

I increased the list to 14 names and ran the macro. It correctly printed all 14 schedules. I think the 2 second delay you added will help ensure that I will be able to print all 300 schedules without overloading the printer.

I can't thank you enough for all your help. This was my first time using this site and you were a God-send.


Report •

#10
December 21, 2012 at 13:41:45
Hey Russel

you're more than welcome. I am very happy I could help.

Having said that, I had a re-think of the approach as I was never comfortable with printing directly from a Macro. Too many things can change and too many external variables (especially printer set ups) exist. My fear is that a lot of time and paper would be wasted dealing with them.

Here is an alternative approach that populates a "Print Output" sheet with all the output and ensures that the paging is correct. You can then visually determine if the output is as you require it and then send that single worksheet to the printer.

I'm hoping you'll check back here to consider this option.

In this option I assigned the name "Form" to the sheet that contains the vlookup results.

Sub RunAll_V3()
    
    Application.ScreenUpdating = False

    PageRows = 27  'Sets the number of rows per page - This could be determined based on the output of the Form sheet as per NoRows
    LastCol = "N"  'Sets the number of columns per page - This could be determined based on the output of the Form sheet as per NoRows
    NoRows = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row   'Determines the number of rows in the list
    Sheets("PrintOutput").Columns("A:" & LastCol).ClearContents 'Clears out the previous output

    Sheets("Form").Select
                
     For i = 1 To NoRows            'loops through each name on the List sheet and places it in the Form sheet
                
                RName = Sheets("List").Cells(i, 1)
                Range("A1") = RName
                
                'Copies the output to the Print Sheet
                
                Range("A1:" & LastCol & PageRows).Copy
                Sheets("PrintOutput").Select
                Range("A" & ((i - 1) * PageRows) + 1).PasteSpecial (xlPasteValues) 'Appends the next page to the Print Sheet
                Range("A" & ((i * PageRows) + 1)).Select
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell 'places a Page Break
                Sheets("Form").Select
        
        Next
        
        Sheets("PrintOutput").Select
        ActiveSheet.PageSetup.PrintArea = "A1:" & LastCol & i * PageRows
        Application.Goto Reference:=Range("A1"), Scroll:=True
            
End Sub


Report •

#11
December 22, 2012 at 10:52:54
Thanks for the additional follow-up. As I stated in post #9, I did change the print command. I just ran a full schedule list with 91 employees and it worked just fine.

Again, thanks for all your help. Next time I get stuck I'll know where to go first.


Report •

#12
December 22, 2012 at 12:28:13
No worries. It was just an alternative approach.

Report •


Ask Question