Creating an array for duplicate records

[maxfli]

I am trying to recreate a multi-page invoice. I have it working as a single page but I think I need to abandon what I have already done use an array to cycle thru the data.

Here is my data.

	A	B	C
	Customer	Invoice	Payment
2	John K Company	3060453	$1
3	John K Company	3060453	$7
4	John K Company	3060453	$1
5	Pete T Company	3060461	$100
6	George S Company	3060500	$200

<tbody>
</tbody>

Notice that the invoice # is repeated for John K Company in rows 2 - 4. This means that invoice has 3 pages. I need code for an array that will cycle thru the first 3 rows and place the Customer, Invoice , and Payment on 3 different Sheets (1) (2) and (3). Once I print this invoice (3060453) it will then continue the array process and in the next case create a single page Invoice on Sheet (1) for Pete T Company. In each case I need a Variable for Page Counter so I can put it on the 1st page (In the first example Page 1 of 3)

Thanks In advance for any help you can provide.
Maxfli

<tbody>
</tbody>

 

[maxfli]

Anyone have a suggestion on this question?

 

[maxfli]

Here is the Code I am currently using:
It works to print a single page Invoice (Worksheet PRINTINVOICE) from (Worksheet DATA). In this case the Invoice #is in column J of a worksheet named DATA.

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub DateToPrintInvoice()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim MyRange As Range, MyVal As Range, LR As Long, CR AsInteger, Desc As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]LR = Sheets("Data").Range("A" &Rows.Count).End(xlUp).Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set MyRange =Sheets("Data").Range("J2:J" & LR)[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]For Each MyVal InMyRange[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]On Error ResumeNext[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]CR = MyVal.Row[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice].Value = MyVal.Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice_2].Value = Cells(CR, 10)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Lead_Days].Value= Cells(CR, 76)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Print_date].Value = Cells(CR, 9)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Due_Date].Value =Sheets("PrintInvoice").[Lead_Days].Value +Sheets("PrintInvoice").[Print_date].Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Lessee].Value = Cells(CR, 2)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Lessee_Address].Value = Cells(CR, 3)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Lessee_Address_2].Value = Cells(CR, 4)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Attn].Value = Cells(CR, 1)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[City_St_zip].Value = Cells(CR, 6)& " ," & Cells(CR, 7) & " " & Cells(CR, 8)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Past_Due30].Value = Cells(CR, 13)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Contract_1].Value = Cells(CR, 26)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Contract_2].Value= Cells(CR, 36)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Contract_3].Value = Cells(CR, 46)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Contract_4].Value = Cells(CR, 56)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Contract_5].Value = Cells(CR, 66)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice_Desc_1].Value = Cells(CR, 27)& " " & Cells(CR, 29)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice_Desc_2].Value = Cells(CR, 37)& " " & Cells(CR, 39)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice_Desc_3].Value = Cells(CR, 47)& " " & Cells(CR, 49)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice_Desc_4].Value = Cells(CR, 57)& " " & Cells(CR, 59)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice_Desc_5].Value = Cells(CR, 67)& " " & Cells(CR, 69)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[PO_1].Value= Cells(CR, 28)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[PO_2].Value = Cells(CR, 38)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[PO_3].Value = Cells(CR, 48)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[PO_4].Value = Cells(CR, 58)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[PO_5].Value = Cells(CR, 68)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Payment_1].Value = Cells(CR, 30)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Payment_2].Value = Cells(CR, 40)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Payment_3].Value = Cells(CR, 50)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Payment_4].Value = Cells(CR, 60)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Payment_5].Value = Cells(CR, 70)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Tax_1].Value = Cells(CR, 31)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Tax_2].Value = Cells(CR, 41)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Tax_3].Value = Cells(CR, 51)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Tax_4].Value = Cells(CR, 61)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Tax_5].Value = Cells(CR, 71)[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]CallSend_To_Output[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next MyVal[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("PrintInvoice").[Invoice].Value =""[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]