VBA macro
Sub Macro1()If MsgBox("Do you want to sort the SEMILAB data now?", vbOKCancel, "Message Box") = vbCancel Then Exit Sub'***Select All Range***ActiveSheet.Range("A1:AC40000").Select'***With All Borders***With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomaticEnd With'***Delete Columns***Columns("A:A").SelectSelection.Delete Shift:=xlToLeft'***Delete Rows***Dim CurrentSheet As Object' Loop through all selected sheets.For Each CurrentSheet In ActiveWindow.SelectedSheets CurrentSheet.Range("1:1,3:3").EntireRow.DeleteNext'***Insert Text***Range("W1").SelectActiveCell = ActiveCell & "Block no"Range("X1").SelectActiveCell = ActiveCell & "Ingot No."Range("Y1").SelectActiveCell = ActiveCell & "Fur ID"Range("Z1").SelectActiveCell = ActiveCell & "Position"Range("AA1").SelectActiveCell = ActiveCell & "Fur Run"Range("AB1").SelectActiveCell = ActiveCell & "Fac"'***Insert Formula***Range("W2").Formula = "=RIGHT(A2,2)"Range("X2").Formula = "=LEFT(A2,11)"Range("Y2").Formula = "=VLOOKUP(X2,Ingot!$A$4:$F$2500,5,FALSE)"Range("Z2").Formula = "=VLOOKUP(X2,Ingot!$A$4:$G$2500,7,FALSE)"Range("AA2").Formula = "=VLOOKUP(X2,Ingot!$A$4:$F$2500,6,FALSE)"Range("AB2").Formula = "=LEFT(A2,6)"End With'***Drag and Drop***Range("W2").AutoFill Destination:=Range("W2:W" & Cells(Rows.Count, 1).End(xlUp).Row)Range("X2").AutoFill Destination:=Range("X2:X" & Cells(Rows.Count, 1).End(xlUp).Row)Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)Range("Z2").AutoFill Destination:=Range("Z2:Z" & Cells(Rows.Count, 1).End(xlUp).Row)Range("AA2").AutoFill Destination:=Range("AA2:AA" & Cells(Rows.Count, 1).End(xlUp).Row)Range("AB2").AutoFill Destination:=Range("AB2:AB" & Cells(Rows.Count, 1).End(xlUp).Row)Dim strText As StringIf MsgBox("Sorting is done!", vbOKOnly, "Message Box") = vbOK Then Exit SubExit SubEnd Sub