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 = xlAutomatic
End With
'***Delete Columns***
Columns("A:A").Select
Selection.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.Delete
Next
'***Insert Text***
Range("W1").Select
ActiveCell = ActiveCell & "Block no"
Range("X1").Select
ActiveCell = ActiveCell & "Ingot No."
Range("Y1").Select
ActiveCell = ActiveCell & "Fur ID"
Range("Z1").Select
ActiveCell = ActiveCell & "Position"
Range("AA1").Select
ActiveCell = ActiveCell & "Fur Run"
Range("AB1").Select
ActiveCell = 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 String
If MsgBox("Sorting is done!", vbOKOnly, "Message Box") = vbOK Then Exit Sub
Exit Sub
End Sub
