Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Hampton, New Hampshire, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help automating the printing process (MS Excel 2002 SP3)

    Hello, I have a worksheet that I developed to assist one of my Benefits folks that takes the indivduals benefits information and prints out a Summary sheet that gets sent to the customer. The problem is that I am trying to automate the printing in this excel file. Currently I have to manually copy one of the lines (#7 on) from "Census" worksheet to line #5. This updates the information displayed on the "Benefit Statement" worksheet which I then print out for one of my Benefits folks to send to their customer.

    I would like to know if there is a way to automate this so that it would just look for data on the line on the "Census" sheet and, if there is any, print it out using the format on the "Benefit Statement" worksheet for all lines on the "Census" worksheet. Yes, the number of lines of data present on the "Census" worksheet will vary. Sometimes there are only 4 or 5 sometimes there are as many as 50.

    Any help or suggestions you care to supply would, as always, be greatly appreciated (read as choccy bars all around)!

    Thanks,
    Attached Files Attached Files
    <font face="Comic Sans MS">MickeyMouse</font face=comic>
    How best to describe the Lounge-
    "Coming together is a beginning, staying together is progress, working together is success"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help automating the printing process (MS Excel 2002 SP3)

    On the Census worksheet:
    - Enter the value 2 in cell A1 (will be changed by the macro).
    - Enter the formula =OFFSET(A5,$A$1,0) in A5, and copy this formula to B5:AC5.

    Create the following macro:

    Sub PrintEm()
    Dim r As Long
    Dim n As Long
    n = Worksheets("Census").Range("A65536").End(xlUp).Row
    For r = 7 To n
    Worksheets("Census").Range("A1") = r - 5
    Worksheets("Benefit Statement").PrintOut
    Next r
    End Sub

    The macro will loop through the rows of the data table and print out a benefit statement for each one.

    Note: there is a typo in D7 on the Benefit Statement sheet (Cutomer instead of Customer).

    See attached version.
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help automating the printing process (MS Excel 2002 SP3)

    How about this?

    Steve

    <pre>Option Explicit
    Sub PrintAll()
    Dim wCensus As Worksheet
    Dim wBenefit As Worksheet
    Dim lRowCopy As Long
    Dim lRowStart As Long
    Dim lRowEnd As Long
    Dim lRow As Long

    'change as desired
    lRowCopy = 5 'Row to Copyto
    lRowStart = 7 'Row data starts in

    Set wCensus = Worksheets("Census")
    Set wBenefit = Worksheets("Benefit Statement")
    With wCensus
    lRowEnd = .Cells(.Rows.Count, 1).End(xlUp).Row
    For lRow = lRowStart To lRowEnd
    .Rows(lRow).Copy .Rows(lRowCopy)
    Application.Calculate
    wBenefit.PrintOut
    Next
    End With
    Set wCensus = Nothing
    Set wBenefit = Nothing
    End Sub</pre>


  4. #4
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Hampton, New Hampshire, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help automating the printing process (MS Excel 2002 SP3)

    Hello Hans,

    Why am I not surprised that you were the first in?!

    This worked perfectly once I added in the 'Options Explicit' bit.

    Thank you very much for your assistance.

    This is for you
    Attached Images Attached Images
    <font face="Comic Sans MS">MickeyMouse</font face=comic>
    How best to describe the Lounge-
    "Coming together is a beginning, staying together is progress, working together is success"

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Hampton, New Hampshire, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help automating the printing process (MS Excel 2002 SP3)

    Hi Steve,

    Thank you as well for your suggestion. I have captured both of them to use.

    I really appreciate all the assistance and information that I get here in the Lounge.

    Here is a little refreshment for you as well...
    Attached Images Attached Images
    <font face="Comic Sans MS">MickeyMouse</font face=comic>
    How best to describe the Lounge-
    "Coming together is a beginning, staying together is progress, working together is success"

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help automating the printing process (MS Excel 2002 SP3)

    Thank you! [burp]

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help automating the printing process (MS Excel 2002 SP3)

    Thanks, but everytime I try to drink it, that icon guy chugs it down... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

Posting Permissions

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