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.

[TABLE="width: 380"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Customer[/TD]
[TD]Invoice[/TD]
[TD]Payment[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John K Company[/TD]
[TD="align: right"]3060453[/TD]
[TD="align: right"]$1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John K Company[/TD]
[TD="align: right"]3060453[/TD]
[TD="align: right"]$7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John K Company[/TD]
[TD="align: right"]3060453[/TD]
[TD="align: right"]$1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pete T Company[/TD]
[TD="align: right"]3060461[/TD]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]George S Company[/TD]
[TD="align: right"]3060500[/TD]
[TD="align: right"]$200[/TD]
[/TR]
</tbody>[/TABLE]

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[TABLE="width: 370"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]