Const MAX_ADV As Integer = 5 

Sub WS_Insales_Macros()
    Call Macros_12
    Call Macros_13
    Call Macros_14
End Sub

 Private Sub Macros_12()
    With ActiveSheet
        Dim y As Long
        y = .Cells(.Rows.Count, "J").End(xlUp).Row
        If y > 1 Then
            With .Range("K2").Resize(y - 1)
                .FormulaLocal = "=Текст(J2;""00"")"
                   
                Dim arr As Variant
                arr = .Value
                '.Value = arr    ”удалить эту строку, если нужна формула, а не значени¤.
            End With
        End If
    End With
End Sub

Private Sub Macros_13()
ActiveSheet.Columns("K").Copy
ActiveSheet.Columns("K").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Private Sub Macros_14()
    With ActiveSheet
    Columns("J").Delete
        End With
End Sub

Sub Test()
    Dim y As Range
    Dim Cell As Range
    With ActiveSheet
        Set y = .Range("J2:J" & .Range("J" & .Rows.Count).End(xlUp).Row)
    End With
    For Each Cell In y
        With Cell
            .NumberFormat = "@"
            .Value = Format(.Value, "00")
        End With
    Next Cell
End Sub

Sub Макрос1()
    Columns("A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub