Using excel to optimize panel stock

[Scott M2]

I was wandering if someone could help me with this.
I have a worksheet that the panel sizes needed to construct 5 piece cabinet doors are entered.
What I am trying to accomplish is to have the same length panels optimized on panels that can not exceed 42 inches in width.
For example If I had 2 panels 20 inches wide and 31 inches long and 3 panels 10 inches wide and 31 inches long.
Then the rough panel size would calculate to be 33 inches long and 41 inches wide with this panel quantity of 1.Then it would calculate the next panel to be 33 inches long and 31 inches wide with this panel quantity of 1
The worksheet that I currently have does this to some degree but I can only get it to work on a width to width bases. In other words I can not seem to figure out how to combine the total widths of the same lengths and come up with workable rough panel widths.
I would be thankful for any advice.

[Scott M2]

Hi all
Today we are snowed in so I was able to semi complete my Glue up tally test workbook
Thanks to ExcelVbaisFun I was able to come up with a algorithm of sorts to list the rough panel glue ups for our 5 piece panel doors
The only thing that I have not ben able to accomplish is numbering the panels in consecutive order i.e.. Panel 1 panel2 and so on for each panel listed in other words this workbook has 28 panels so I would like each panel numbered 1 thru 28 respectively. Do you have any suggestions on how to accomplish this?

[Scott M2]

Ok I think I have the workbook working pretty well thanks to my friend Ali and Dan from ExcelVbaisFun
Here is the wb if any one is interested. Also If you have any suggestions on how to make it better please post.

[Alf]

Good of you to post the final solution to your problem!

Looking at the macro perhaps one could say that since you almost at the start activate sheet1 you don't need to refer to this sheet i.e.

Please Login or Register  to view this content.

just writing

Please Login or Register  to view this content.

will work just as well

The line

Please Login or Register  to view this content.

could be written as

Please Login or Register  to view this content.

as this will find the last used row in column B instead of setting a high number to ensure that the B column is cleared.

Please Login or Register  to view this content.

could be written as

Please Login or Register  to view this content.

where the offset command starts clearing the used range starting from the 5th column and here I use Sht1 as this is easier to write than "ActiveSheet.UsedRange".

So really no improvement of your present code just showing different way of writing some parts of it.

Alf

[Scott M2]

Thanks Alf the advice you gave worked well