Thank you TehneXus! That formula worked perfectly! I have one last issue with this document though.... is there a way to edit this formula, to only allow you to select the "Setup Die Table" button once? any time that the Macro has another prompt attempt after the first time its ran would need a Pop up Display saying "Die Tables are Only allowed on the Initial Setup", and the Macro should not be ran...I hope this makes sense. Please let me know if you need clarity.
latest bom.xlsm
here is the Macro,
Private Sub CommandButton1_Click()
Dim bomSH As Worksheet
Set bomSH = Sheets("BOM")
Sheets("Input-Output Values").Activate
'clear out any existing data
Range(Range("I9"), Range("M" & WorksheetFunction.Max(9, Cells(Rows.Count, "I").End(xlUp).Row))).Delete
With bomSH
.Range(.Cells(1, 42), .Cells(1, WorksheetFunction.Max(42, .Cells(1, Columns.Count).End(xlToLeft).Column))).EntireColumn.Delete
End With
For i = 2 To Range("B5").Value
outcoll = bomSH.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
outrow = Cells(Rows.Count, "I").End(xlUp).Offset(2, 0).Row
Range("I1:M7").Copy Destination:=Cells(outrow, "I")
Cells(outrow, "I").Replace what:=1, replacement:=i
repl_col = Cells(1, outcoll + 6).Address(rowabsolute:=False, columnabsolute:=False)
repl_col = Left(repl_col, 2)
Cells(outrow + 1, "J").Resize(5, 1).Replace what:="AL", replacement:=repl_col
Cells(outrow + 1, "J").Resize(5, 1).Copy Destination:=Cells(outrow + 1, "J").Resize(5, 4)
With bomSH
.Range("AF1:AO6").Copy Destination:=.Cells(1, outcoll)
.Cells(5, outcoll).Value = "Die " & i
.Range("AL8").Copy Destination:=.Cells(8, outcoll + 6)
.Range("AL9").Copy Destination:=.Cells(9, outcoll + 6)
.Range("AG13").Copy Destination:=.Cells(13, outcoll + 1)
.Range("AH13").Copy Destination:=.Cells(13, outcoll + 2)
.Range("AM13").Copy Destination:=.Cells(13, outcoll + 7)
.Range("AN13").Copy Destination:=.Cells(13, outcoll + 8)
End With
Next i
End Sub
Cansa
Bookmarks