Option Explicit
Sub Macro1()
Dim ws As Worksheet
For Each ws In Worksheets
With ws
.Columns("A:A").Copy Destination:=.Columns("E:E")
.Columns("E:E").Replace What:=" (****)", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Range("F1:F25").FormulaR1C1 = "=MID(RIGHT(RC[-5],6),2,4)"
.Columns("B:B").Copy Destination:=.Columns("G:G")
End With
Next ws
End Sub