Macro Needed to Properly Create Tables

[Cansa]

Hi All,

I'm hoping that someone who is Macro knowledgeable could help me with a few tweaks that our Tooling Dept needs done..does anyone know how to adjust the Macro to meet these needs,
*These needs must be met for the Macro that is on the "Setup Die Tables" Button on the Input-Output Sheet*


There are columns that need to be inserted per Die on the BOM sheet, Col Q is labelled "1" for Die 1. We would also need Columns added for the Number of Dies entered (e.g. Col R labelled "2" [depending on the number of dies entered in B5 on the Input-Output Sheet...]).

The Formulas in Col N on the Input-Output Sheet are not correlating to the Die Tables that are updated...All of the other formulas are correlating okay. (I'm hoping this would be an easy fix).

Thirdly, I need to make sure that the Macro, and the Formulas still update properly, even after Columns are Added/inserted on the BOM sheet. So an issue for example, if i insert a column after Col A...then enter 2 Dies on the Input-Output Sheet and run my Macro, the formulas no longer correlate on their Die Tables...

latest bom.xlsm

Your help is greatly valued!! Please let me know if you need any clarification on anything here....

Best regards,
Cansa

[AlphaFrog]

1.) Unmerge all the Column Labels in row 5 on sheet BOM. Use Horizontal Alignment: Center Across Selection instead. It looks the same. Merged cells cause great headaches when inserting columns and in this case are not necessary.

2.) Create a named range (e.g.; Die1_All) for the Die 1 area on sheet BOM and also for the subregions in Die 1. Name them all starting with Die1_???. Then in the code reference the named ranges e.g.; .Range("Die1_All"). If you then insert a column, the named ranges automatically update and you don't have to change your code.

3.) When you copy Die1_All for Die 2, Die 3 ...etc., have the code also create new named ranges for Die 2, Die 3.

4.) Use the Named Ranges in the formulas on sheet Input-OutPut. Then in the code when you copy the WIRE Area, you can just replace within the formulas "Die1" with "Die2" .

Attached is your workbook with the updated code. I have an older Excel Version. CommandButtons don't survive the conversion, but you'll see how it works

Bom.xls

[Cansa]

Hi AlphaFrog,

Your Coding worked really great in your document...although I seem to have a few minor issues

When i copy and paste your code i have an error message popping up stating..Run-time error '1004': Method 'Range' of object "_worksheet'...

When the Data Tables copy to create tables (Input Output Sheet) and to create Columns (Bom Sheet) the numbers from die 1 are copied also...is there a way to copy over the new Dies with Blanks or Zeros instead of having the Data from Die 1 Copied over?

Last but not least, I have formulas on the BOM Sheet that Auto Fill when Data is entered into Col A...when new Columns are inserted my Auto-fill formulas for Columns AX ((=IFERROR(AT8*AU8,"")) and BA ((=IFERROR(AV8/AW8,"")) become re-positioned...

This is a really important Document need for out department so your help is huge..

latest bomAlphaFrog1.xlsm

Regards,
Cansa

[AlphaFrog]

1.) Which line errors? Is a line highlighted when you debug?

2.) Use something like this to clear the numeric constant values from the pasted Die Table

Please Login or Register  to view this content.

3.) Create a Name for columns AX & BA. Reference those names in your code instead of a specific column number. If you insert a new column, the Named ranges automatically update and your code references the correct columns.

[FDibbins]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Cansa]

1. Line 21 is the highlighted line that appears when i go into the Macro to Debug
2. I will be able to add the coding you provided after the debug issue is resolved
3. I am not too familiar with the coding to make the Macro look for Column Names instead of the Column Number, I'll still give it a try (Unfortunately i am still a rookie in the Macro World lol).