Sub Update_Selection()' Update_Selection Macro
Range("Selections").ClearContents
With Sheets("Back")
.Range("Units").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("Criteria"), _
CopyToRange:=.Range("Extract"), Unique:=True
.Range("F_UnitName").Copy
End With
Sheets("Main").Range("Name").PasteSpecial Paste:=xlPasteValues
Sheets("Main").Range("Options").Cells.SpecialCells(xlCellTypeAllValidation).ClearContents
Dim rngDst As Range
Dim rngSrc As Range
Dim arr As Variant
Dim I As Long
Application.ScreenUpdating = False
arr = Array("F", "F_Target", "Filters", "CP", "CP_Target", "Control_Panels", _
"M", "M_Target", "Mounting", "CM", "CM_Target", "Cooling_Modules", _
"HS", "HS_Target", "Heating_Surfaces", _
"S", "S_Target", "Sensors", "BMS", "BMS_Target", "BMS", _
"CS", "CS_Target", "Condensation", "EM", "EM_Target", "Energy_Meter", _
"PWO", "PWO_Target", "Panels_WO", "PW", "PW_Target", "Panels_W", _
"OG", "OG_Target", "Outside_Grilles", "FC", "FC_Target", "Facade_Cover")
For I = LBound(arr) To UBound(arr) Step 3
With Sheets("Options")
Set rngSrc = .Range(Replace(Sheets("Main").Range("UnitType").Value, " ", "_") & "_" & arr(I))
Set rngDst = .Range(arr(I + 1))
End With
rngSrc.Copy
rngDst.PasteSpecial xlPasteValues
With ActiveWorkbook.Names(arr(I + 2))
.Name = arr(I + 2)
.RefersToR1C1 = "=Options!" & rngDst.Resize(rngSrc.Rows.Count, 1).Address(, , xlR1C1)
End With
Application.DisplayAlerts = False
Next I
'If Range("UnitType").Value = "AM 100" _
'Then Range("AM_100_Prices") = Range("Base_Price")
Range("Description_Target").Select
Range("Description_Target").MergeArea.ClearContents
Selection.MergeCells = False
Range("Price_Target_A").Value = "-"
Range("Unit_Price_Target_A").Value = "-"
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.Goto Sheets("Main").Range("Name")
End Sub