Excel help needed

[Shirohana]

Trying to create a macro and it's doing my head in.

 

Two different sheets.

First one is pasted from a different document.

The second copies a column (only amounts) from the first sheet and converts a full stop to a comma with a formula.

 

The problem I'm having is that the number column (Column L, Sheet "Print") needs to be sorted.

Now because there's a formula in that column, I'm trying to copy -> paste values through the following (recorded) macro:

 

Sub Macro5()
'
' Macro5 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
   Columns("L:L").Select
   Application.CutCopyMode = False
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
       xlNone, SkipBlanks:=True, Transpose:=False
   Columns("A:L").Select
   Application.CutCopyMode = False
   ActiveWorkbook.Worksheets("Print").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Print").Sort.SortFields.Add Key:=Range("L1:L700") _
       , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Print").Sort
       .SetRange Range("A1:L700")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
   Range("A1").Select
End Sub

 

The problem is, that after copying and pasting values the column needs to be converted to a number (manually clicking "Convert to number" during recording doesn't seem to register).

 

Can anyone help?

[KentyRed]

This post is not viewable to guests.

You can sign in to your account at the login page here

If you do not have an account then you can register here

[Shirohana]

This post is not viewable to guests.

You can sign in to your account at the login page here

If you do not have an account then you can register here

[KentyRed]

This post is not viewable to guests.

You can sign in to your account at the login page here

If you do not have an account then you can register here