Sep 03 2022 07:15 AM
Sub Clean()
'
' Clean Macro
'
Dim FirstCellAddress As String
Dim LastCellAddress As String
FirstCellAddress = ActiveCell.Address
LastCellAddress = ActiveCell.SpecialCells(xlLastCell).Offset(0, 1).Address
ActiveCell.SpecialCells(xlLastCell).Offset(0, 1).Activate
Range(FirstCellAddress & ":" & LastCellAddress, ActiveCell).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1], 'ACS2000 Reeference tables.xlsx'!Table_a2000cdc_hwunit_modes_1[[hwunit_mode_num]:[hwunit_mode_name]],2,FALSE)"
ActiveCell.Select
Selection.Copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
End Sub