Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sequential print from list

    I have a spreadsheet with a form letter. I currently use a drop down list to select a name which then uses Vlookup to add the correct mailing address for each name. I then print the letter each time I change name and address, one at a time.

    Can someone help me with a macro to select a name from a separate list in a named range one at a time, print the letter and select the next name until the end of the list. Each time the name will fill in the correct address before printing. Any help would be appreciated.

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have attached a sample spreadsheet of what I am after. Hope this helps.
    Attached Files Attached Files

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI your With...End With statement currently has no effect since you have not qualified the Cells calls. You need
    Code:
        With Worksheets("cus data")
        .Cells(12, 2).Value = name.Value
        .Cells(13, 2).Value = name.Offset(0, 1).Value
        .Cells(14, 2).Value = name.Offset(0, 2).Value
        .Cells(15, 3).Value = name.Offset(0, 3).Value
        Worksheets("Letter").PrintOut 'CHANGE TO THE SHEET YOU WANT PRINTED OUT
        End With
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Maud for your help. The reason I wanted to use a dropbox was to be able to select a subset of names or just one name, from my customer data file. This way I need not print every customer letter each time. Do you have any suggestion?

    Thanks again Al

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maud,

    Doesn't the "print all" button print all customers listed in the customer database sheet? I was looking to print only the subset selected by the dropdox on the Letter sheet.

    Al

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    AlRom,

    Here's a variant that lets you multi-select the customer names on the "cus data" sheet which should eliminate typing errors not to mention typing. Just another way to accomplish the same thing.
    Code:
    Option Explicit
    
    Public Sub PrintLtrs()
    
      Dim lCustCnt As Long
      Dim lCntr    As Long
      Dim vCells   As Variant
      Dim wksCust  As Worksheet
      
      Worksheets("cus data").Select
      Set wksCust = ActiveSheet
      lCustCnt = Selection.Cells.Count
      vCells = Split(Selection.Address(, , xlA1), ",")
      
      Worksheets("Letter").Select
      
      For lCntr = 0 To lCustCnt - 1
      
        With wksCust.Range(vCells(lCntr))
            If .Value <> "" Then
              Cells(12, 2).Value = .Value               'Name
              Cells(13, 2).Value = .Offset(0, 1).Value  'Street
              Cells(14, 2).Value = .Offset(0, 2).Value  'City
              Cells(15, 2).Value = .Offset(0, 3).Value  'Zip
              Worksheets("Letter").PrintOut 'CHANGE TO THE SHEET YOU WANT PRINTED OUT
            End If
        
        End With 'wksCust
        
       Next
       
    End Sub
    Note: this also eliminates the printing of the names box and I set the properties of the print button to do not print.
    HTH
    Attached Files Attached Files
    Last edited by RetiredGeek; 2013-02-11 at 19:33.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Maud and RetiredGeek, I appreciate the work you have done but I still struggle with the worksheet "auto letter revised 3". The pint button seems to be hidden by the select name box. I can't move the box or the print button. The letter has a range name "Letter" which would is what I want printed, as it prints now it prints the active worksheet. Could I ask you to have one more look at it and suggest some changes or if you could tell me how to move the print button or the box. I experience has been limited to auto macro mode and creating a button to which I assign the macro. What you are showing me is new to me, please bear with me.

    Thanks Al

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maud, not working for me. I am using excel 2007 if that makes a difference. I think I will play around with it and try a few different things. At this point I will not ask you to do any more work on it. My original design let me print one at a time using a validation dropbox which is something I understand and can use.

    Thanks so much for your help.

    Al

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    AlRom,

    Ok I revised my version to only print the range called "letter".

    Just for clarification here's what I meant by multi select.MultiSelect.PNG

    Note: I had to highlight the selected cells in Yellow as the normal Excel highlighting didn't show up on the screen capture.

    Once you have the ones to print highlighted switch the the Letter sheet and press the button.

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Here's another way of doing it.
    ..Using Form buttons rather than ActiveX buttons.
    And I like using a clicker for selecting the next Customer.
    What do you think?

    zeddy
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    RetiredGeek, I can use this approach but what if we place an "X" in column A beside the ones to print and then remove the "X"'s after the print job is done?

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    AlRom,

    Why do you want to place an X? All you have to do is hold the Ctrl Key and click on the ones you want to print or if you want to print them all click on the first one then hold Shift and Click on the last one.

    I can change the code to do the X thing if you really want it that way.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    One thing you should be aware of:
    When you use VBA to print such letters in Excel, the VBA can process at such a fast pace that the screen updates required for each 'customer' can outpace the ability of a printer to keep up.
    When this was first reported to me, another person had simply added a 'wait-for-one-second' within the VBA code to artificially 'delay' the VBA routine, allowing each 'printout' to be sent correctly. Otherwise, the output letters were found to have 'skipped' a number of required 'customers' because the VBA was so fast.
    Which is why I added a DoEvents command to the VBA routine.

    Perhaps for a few dozen customers it may not be needed.
    Also, as a matter of choice, using Form buttons on worksheets, rather than ActiveX buttons, causes fewer problems with Excel.

    zeddy

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by zeddy View Post
    Hi

    One thing you should be aware of:
    When you use VBA to print such letters in Excel, the VBA can process at such a fast pace that the screen updates required for each 'customer' can outpace the ability of a printer to keep up.
    When this was first reported to me, another person had simply added a 'wait-for-one-second' within the VBA code to artificially 'delay' the VBA routine, allowing each 'printout' to be sent correctly. Otherwise, the output letters were found to have 'skipped' a number of required 'customers' because the VBA was so fast.
    Which is why I added a DoEvents command to the VBA routine.
    Zeddy,

    Very good point!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •