Macro to Auto-populate cells and columns based off of a Number

Hi all,
I need a Macro that will be prompted by the Number input in B5 on the sheet "input-output".

If the Input is 2 then the Macro needs to create all Tables/Columns needed for 2 Dies….
If the Input is 3 then the Macro needs to create all Tables/Columns needed for 3 Dies….
There will be a Max of 6 possible Dies, so the Macro will only need to go that far.

First, on the BOM sheet the Macro must copy AF1-AO6, and paste to create Columns for Die 2 [it would not be good to have the macro copy entire columns because then the Die 1 data would be copied into Die 2]. Please note that the formulas, in Die 1, would also be needed in the Die 2 Columns - These Formulas happen to be written in another Macro that Auto fills whenever Data is entered in Col A.

Second, on the Input-Output sheet the Macro must Set Up a Table, with correlating formulas, for Die two (e.g. I9:M15).

This is a pretty intense Macro and im hoping that these things are possible....A lot of the Formulas that are needed are written in another Macro so I'm hoping they can be carried over and work properly for the additional Die information.

Regards,

Cansa

Hi

I've put the code below into a general module.

A couple of things
1) Change the formula in Input-Output Values!J2:J5 so that the bom!I:I is made absolute (BOM!$I:$I)
2) there will be NO existing outputs for any of the new die requirements (no die2, 3...6) in either sheet
3) There WILL be entries for DIE 1

Please Login or Register  to view this content.

HTH

rylo

Wow, Rylo you are a genius! It works perfectly :-). Thank you for all of your help!!

Cansa

Hi Rylo,

I ran into a slight problem as i was using the Macro you had made...the number that gets entered into B5 on the input-output sheet, will be constantly updated/changed....is there any way to have the tables refresh every time the number changes, instead of having them make extra duplicates?? Attached i have the current spreadsheet demonstrating the issue...
latest bom.xlsm

Regards,
Cansa

Hi

So restriction (2) I put in post #2 does not apply? You aren't starting with a greenfields sheet each time the macro is run? How about the other restrictions? Will there always be a default output for DIE 1 EVERY time the macro is run?

rylo

There will always be at least 1 Die when this document is used, so restriction #3 works, Die 1 will always be used....the number of Dies after that will always be adjusting or increasing in Number (max 6) as the job continues..so restriction #2 may not work for what is needed... The document is one that will continually be being updated until the job is complete. Sorry for the late clarification! Please let me know if you have any further questions. Is there a way to have the Macro completely refresh or loop every time a number is entered into B5? In any case I hope there is a way to make it work, the Macro is very close to doing all that is needed,

Regards,
Cansa

Hi

OK, updated with some code to clear out any existing "extra" content.

Please Login or Register  to view this content.

rylo

That Macro works very well, there is only one Debugging issue that i am running into on occasion....attached, if you hit the "Setup Die Tables" button (which prompts your Macro) you'll notice the debugging message pops up...i Noticed that when i had Deleted (instead of clearing) the columns on the BOM page the Macro worked fine afterwards. Is it possible to have the Macro Delete those Columns on the BOM page instead of clearing them out?
latest bom.xlsm

Regards,
Cansa

Hi

OK, with delete

Please Login or Register  to view this content.

rylo