Create Worksheets based on a table

maxfli

New Member
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?

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I am using Office Excel 365.

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!

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.

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.

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

Last edited:

Replies
6
Views
153
Replies
5
Views
78
Replies
13
Views
466
Replies
0
Views
121
Replies
0
Views
231

1,203,079
Messages
6,053,408
Members
444,662
Latest member
AaronPMH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back