Select mutiple entries and copy matching cells onto Invoice sheet

**Yorksboy** · 10-26-2010, 06:30 AM

I'm trying to find some code which will seach down column A on 'Invoice Data' sheet for a particular reference number (may be multiple occurances) and then copy the corresponding values from columns D, E, F & G on to the invoice template.

In the first instance, the reference number will be selected by the user, but my aim eventually, is to enter a date and for Excel to produce the invoices for all order references on that date.

Any help would be appreciated.

Example attached.

**peterjuhnke** · 10-26-2010, 06:59 AM

Hello Yorksboy
Were did you want the search number to go?
Can you give an example please
Regards
Peter

**Marcol** · 10-26-2010, 07:19 AM

This basic code might give you a starting point

Option Explicit

Sub ListInvoices()
    Dim strInvoiceNo As String

    strInvoiceNo = Application.InputBox("Select Invoice to List")

    Sheets("Invoice").Range("A20:D36").ClearContents
    
    Range("$A$1:$G$75").AutoFilter Field:=1, Criteria1:=strInvoiceNo
    Range("A2:A10").Copy


    Sheets("Invoice").Range("A20").PasteSpecial xlValues
    Range("E2:G10").Copy
    Sheets("Invoice").Range("B20").PasteSpecial xlValues

    Range("$A$1:$G$75").AutoFilter
    
End Sub

Assign this to your button.

Click on the invoice number you want to print on the Inputbox prompt

Hope this helps

**Yorksboy** · 10-26-2010, 07:54 AM

Peter,

The search number is purely used to select which rows needs to go onto the invoice, so you could say that it's a temporary variable.

**Yorksboy** · 10-26-2010, 07:56 AM

Thanks Marcol,

That's certainly got me started.

regards,

Mark

P.S. Just had a thought, this will work OK as long as as I don't have too many tickets for one order ref!

Originally Posted by Marcol

This basic code might give you a starting point

Option Explicit

Sub ListInvoices()
    Dim strInvoiceNo As String

    strInvoiceNo = Application.InputBox("Select Invoice to List")

    Sheets("Invoice").Range("A20:D36").ClearContents
    
    Range("$A$1:$G$75").AutoFilter Field:=1, Criteria1:=strInvoiceNo
    Range("A2:A10").Copy


    Sheets("Invoice").Range("A20").PasteSpecial xlValues
    Range("E2:G10").Copy
    Sheets("Invoice").Range("B20").PasteSpecial xlValues

    Range("$A$1:$G$75").AutoFilter
    
End Sub

Assign this to your button.

Click on the invoice number you want to print on the Inputbox prompt

Hope this helps