Printing multiple pages of variable info using a single template.

[smk224]

Hello experts and members!

I'd appreciate some assistance with an issue I'm having with Excel.

I have an issue with a client invoice system I’m trying to create on Excel 2003.

I have created an invoice template, but the data such as Client Name, Address, Monthly Billing are all variables stored in a different worksheet in a list.

Each row of this customer list contains information for each of many clients.

The issue comes in when I want to figure out how I can expedite printing invoices for several clients at the push of one button.

There is one worksheet that serves as a template, but upon print command, I would like this template to look through the client list and search for rows that are checked with a checkmark and print only those selected clients.

If I select 10 clients out of 100, it would be great to print 10 invoices at the push of a single button using data stored in the client list worksheet.

I’m assuming that this is a fairly difficult task. Is it even possible?

I’d appreciate any help I can get, including which related sources I can see and study from it even.

Thank you very much.

Please see the attached file.

[protonLeah]

One way is to use Checkboxes from the Forms Control, setting the reference cell for the CB to the cell underneath the CB. Then you use LOOPING code to check the range of cells in the column for TRUE or FALSE. If TRUE then that row would be used to fill the template and print...

[smk224]

thank you for your prompt reply.

I however don't have knowledge of coding so please provide me with some coding assistance if that's ok. I have attached a file in the original post in case your reply came before I made the attachment.

Thanks =)

[protonLeah]

This macro works with your example:

Option Explicit
Sub testprinter()
    Dim CLIENTS     As Worksheet, _
        INVOICE     As Worksheet, _
        PrintRange  As Range, _
        PrintBox    As Range, _
        RECNUM      As Range, _
        COMPANY     As Range, _
        ADDRESS     As Range, _
        MONTHLY     As Range, _
        LastRow     As Long
        
    Set CLIENTS = Sheets("clientlist")
    Set INVOICE = Sheets("invoice template")
    
    Set RECNUM = INVOICE.Range("B4")
    Set COMPANY = INVOICE.Range("A6")
    Set ADDRESS = INVOICE.Range("A7")
    Set MONTHLY = INVOICE.Range("D13")
    
    'count the number of cells in column a linked to check boxes
    LastRow = CLIENTS.Cells(Rows.Count, "A").End(xlUp).Row
    
    'set up the range of cells in column a linked to checkboxes
    Set PrintRange = CLIENTS.Range("A3:A" & LastRow)
    
    'loop through the range of linked cells for TRUE (i.e., box is checked, so print row)
    For Each PrintBox In PrintRange
        If PrintBox.Value = True Then
        
            'copy data into the template
            RECNUM.Value = PrintBox.Row - 2
            COMPANY.Value = PrintBox.Offset(0, 1).Value
            ADDRESS.Value = PrintBox.Offset(0, 2).Value
            MONTHLY.Value = PrintBox.Offset(0, 3).Value
            
            INVOICE.PrintOut Copies:=1
        End If
    Next PrintBox
End Sub

[smk224]

This macro works with your example:

Option Explicit
Sub testprinter()
    Dim CLIENTS     As Worksheet, _
        INVOICE     As Worksheet, _
        PrintRange  As Range, _
        PrintBox    As Range, _
        RECNUM      As Range, _
        COMPANY     As Range, _
        ADDRESS     As Range, _
        MONTHLY     As Range, _
        LastRow     As Long
        
    Set CLIENTS = Sheets("clientlist")
    Set INVOICE = Sheets("invoice template")
    
    Set RECNUM = INVOICE.Range("B4")
    Set COMPANY = INVOICE.Range("A6")
    Set ADDRESS = INVOICE.Range("A7")
    Set MONTHLY = INVOICE.Range("D13")
    
    'count the number of cells in column a linked to check boxes
    LastRow = CLIENTS.Cells(Rows.Count, "A").End(xlUp).Row
    
    'set up the range of cells in column a linked to checkboxes
    Set PrintRange = CLIENTS.Range("A3:A" & LastRow)
    
    'loop through the range of linked cells for TRUE (i.e., box is checked, so print row)
    For Each PrintBox In PrintRange
        If PrintBox.Value = True Then
        
            'copy data into the template
            RECNUM.Value = PrintBox.Row - 2
            COMPANY.Value = PrintBox.Offset(0, 1).Value
            ADDRESS.Value = PrintBox.Offset(0, 2).Value
            MONTHLY.Value = PrintBox.Offset(0, 3).Value
            
            INVOICE.PrintOut Copies:=1
        End If
    Next PrintBox
End Sub

Thank you so much the time you put in to help me with this! I really appreciate it.

I will try this out and get back to this post.

[smk224]

I have worked with the macro you've provided and it was exactly what I was looking for. Thank you.

I however have one more function I'd like to add to the client list page but don't know how to make the macro.

I have added 4 radio buttons, Select All, Select None, Select Good Clients, Select Bad Clients.

Depending on the group set for each row on E3 to E5, how can I make this happen?

The excel file is attached with the radio buttons (albeit no macros)

Thanks ^.^