Create Worksheets based on a table

[maxfli]

I have a table that looks like this:

Company	Invoice	Amount 1	Amount 2	Amount 3	Amount 4	Amount 5	Page	Last Page
John Q Company	3063615	$ 101	$ 200	$ 25	$ 25	$ 25	1	1
Peter North Company	3063616	$ 999	$ 877	$ 66	$ 66	$ 66	1	1
Sam South Company	3063617	$ 205	$ 180	$ 154	$ 164	$174	1	7
Sam South Company	3063617	$ 333	$ 195	$ 225	$ 375	$ 22	2	7
Sam South Company	3063617	$ 777	$ 987	$ 444	$ 200	$ 33	3	7
Sam South Company	3063617	$ 105	$ 111	$ 555	$ 344	$ 44	4	7
Sam South Company	3063617	$ 116	$ 137	$ 555	$ 65	$ 55	5	7
Sam South Company	3063617	$ 140	$ 245	$ 666	$ 242	$ 66	6	7
Sam South Company	3063617	$ 150	$ 333	$ 666	$ 255	$ 77	7	7
Sam South Company	3063618	$ 126	$ -	$ -	$ -	$ -	1	1
New York East Company	3063619	$ 5,905	$ -	$ -	$ -	$ -	1	1

<tbody>
</tbody>

I have a COUNTIF formula and a MAX formula that will count the number of repeating invoice numbers. So the last 2 columns are the page number and the last page number. I need some kind of VB code that will do a FOR NEXT (?) loop in the case of invoice 3063617 it will copy an existing worksheet call ADDPAGE and rename it to PAGE 2, 3, 4 for each of pages 2 thru 7. I also need to populate amount field on each page. Page 1 is already created and assumed for all invoices.

Any Ideas?

 

[maxfli]

I am using Office Excel 365.

 

[maxfli]

I have a table that looks like this:

Company	Invoice	Amount 1	Amount 2	Amount 3	Amount 4	Amount 5	Page	Last Page
John Q Company	3063615	$ 101	$ 200	$ 25	$ 25	$ 25	1	1
Peter North Company	3063616	$ 999	$ 877	$ 66	$ 66	$ 66	1	1
Sam South Company	3063617	$ 205	$ 180	$ 154	$ 164	$174	1	7
Sam South Company	3063617	$ 333	$ 195	$ 225	$ 375	$ 22	2	7
Sam South Company	3063617	$ 777	$ 987	$ 444	$ 200	$ 33	3	7
Sam South Company	3063617	$ 105	$ 111	$ 555	$ 344	$ 44	4	7
Sam South Company	3063617	$ 116	$ 137	$ 555	$ 65	$ 55	5	7
Sam South Company	3063617	$ 140	$ 245	$ 666	$ 242	$ 66	6	7
Sam South Company	3063617	$ 150	$ 333	$ 666	$ 255	$ 77	7	7
JC Junk and Company	3063618	$ 126	$ -	$ -	$ -	$ -	1	1
New York East Company	3063619	$ 5,905	$ -	$ -	$ -	$ -	1	1

<tbody>
</tbody>

I have a COUNTIF formula and a MAX formula that will count the number of repeating invoice numbers. So the last 2 columns are the page number and the last page number. I need some kind of VB code that will do a FOR NEXT (?) loop in the case of invoice 3063617 it will copy an existing worksheet call ADDPAGE and rename it to PAGE 2, 3, 4 for each of pages 2 thru 7. I also need to populate amount field on each page. Page 1 is already created and assumed for all invoices.

Does anyone have any ideas on this question. I am probably not making myself clear. The Code needs to copy (Create) the ADDPage Worksheet for each page until the PAGE Column H2 = the LASTPAGE I2. I already have a routine that will print the invoice. In the case of the 7 page invoice when H2 = I2 it will call the print routine and then clean up the pages 2 - 7 and go on to the next record.

I appreciate any help!

 

[duggie33]

I have never done this myself but it looks like others have asked similar questions...

Google this... [ site:www.mrexcel.com "Create Worksheets" ].

There are 72 results on this forum. One of them surely would give you a direction to go.

 

[maxfli]

Yea I have been working on that exact search. The issue I have is not being able to incorporate is the idea of Copying (creating) the ADDPAGE Sheet using variables For the Current Page (Column H) to the Last Page (Column I). So essentially on invoice 3063617 I would add pages 2-7 and then call a print subroutine.

 

[duggie33]

Something like a Do While "Page" is <= "Last Page" or as you loop through invoice numbers run an If Then on page vs. last page; less than or equal to adds sheet, when equal add sheet and print.

Hope that helps,

Doug