Create Worksheets based on a table

M

maxfli

New Member
Joined
Sep 14, 2010
Messages
26
I have a table that looks like this:
[TABLE="width: 854"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Invoice[/TD]
[TD] Amount 1[/TD]
[TD] Amount 2[/TD]
[TD] Amount 3[/TD]
[TD] Amount 4[/TD]
[TD] Amount 5[/TD]
[TD]Page[/TD]
[TD]Last Page[/TD]
[/TR]
[TR]
[TD]John Q Company[/TD]
[TD="align: right"]3063615[/TD]
[TD] $ 101[/TD]
[TD] $ 200[/TD]
[TD] $ 25[/TD]
[TD] $ 25[/TD]
[TD] $ 25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Peter North Company[/TD]
[TD="align: right"]3063616[/TD]
[TD] $ 999[/TD]
[TD] $ 877[/TD]
[TD] $ 66[/TD]
[TD] $ 66[/TD]
[TD] $ 66[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 205[/TD]
[TD] $ 180[/TD]
[TD] $ 154[/TD]
[TD] $ 164[/TD]
[TD] $174[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 333[/TD]
[TD] $ 195[/TD]
[TD] $ 225[/TD]
[TD] $ 375[/TD]
[TD] $ 22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 777[/TD]
[TD] $ 987[/TD]
[TD] $ 444[/TD]
[TD] $ 200[/TD]
[TD] $ 33[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 105[/TD]
[TD] $ 111[/TD]
[TD] $ 555[/TD]
[TD] $ 344[/TD]
[TD] $ 44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 116[/TD]
[TD] $ 137[/TD]
[TD] $ 555[/TD]
[TD] $ 65[/TD]
[TD] $ 55[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 140[/TD]
[TD] $ 245[/TD]
[TD] $ 666[/TD]
[TD] $ 242[/TD]
[TD] $ 66[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 150[/TD]
[TD] $ 333[/TD]
[TD] $ 666[/TD]
[TD] $ 255[/TD]
[TD] $ 77[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063618[/TD]
[TD] $ 126[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]New York East Company[/TD]
[TD="align: right"]3063619[/TD]
[TD] $ 5,905[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
maxfli said:
I have a table that looks like this:
[TABLE="width: 854"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Invoice[/TD]
[TD] Amount 1[/TD]
[TD] Amount 2[/TD]
[TD] Amount 3[/TD]
[TD] Amount 4[/TD]
[TD] Amount 5[/TD]
[TD]Page[/TD]
[TD]Last Page[/TD]
[/TR]
[TR]
[TD]John Q Company[/TD]
[TD="align: right"]3063615[/TD]
[TD] $ 101[/TD]
[TD] $ 200[/TD]
[TD] $ 25[/TD]
[TD] $ 25[/TD]
[TD] $ 25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Peter North Company[/TD]
[TD="align: right"]3063616[/TD]
[TD] $ 999[/TD]
[TD] $ 877[/TD]
[TD] $ 66[/TD]
[TD] $ 66[/TD]
[TD] $ 66[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 205[/TD]
[TD] $ 180[/TD]
[TD] $ 154[/TD]
[TD] $ 164[/TD]
[TD] $174[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 333[/TD]
[TD] $ 195[/TD]
[TD] $ 225[/TD]
[TD] $ 375[/TD]
[TD] $ 22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 777[/TD]
[TD] $ 987[/TD]
[TD] $ 444[/TD]
[TD] $ 200[/TD]
[TD] $ 33[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 105[/TD]
[TD] $ 111[/TD]
[TD] $ 555[/TD]
[TD] $ 344[/TD]
[TD] $ 44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 116[/TD]
[TD] $ 137[/TD]
[TD] $ 555[/TD]
[TD] $ 65[/TD]
[TD] $ 55[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 140[/TD]
[TD] $ 245[/TD]
[TD] $ 666[/TD]
[TD] $ 242[/TD]
[TD] $ 66[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sam South Company[/TD]
[TD="align: right"]3063617[/TD]
[TD] $ 150[/TD]
[TD] $ 333[/TD]
[TD] $ 666[/TD]
[TD] $ 255[/TD]
[TD] $ 77[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]JC Junk and Company[/TD]
[TD="align: right"]3063618[/TD]
[TD] $ 126[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]New York East Company[/TD]
[TD="align: right"]3063619[/TD]
[TD] $ 5,905[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

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.
Click to expand...

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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
286
jsand
J
I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
665
ItalianPlatinum
I
S
  • Question Question
Create new sheets based on criteria in a table and value in a cell
Replies
6
Views
445
Herakles
H
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
159
ellison
E
D
  • Question Question
Formula to create a list based
Replies
2
Views
103
myall_blues
myall_blues

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top