Jump to content
By fans, for fans. By fans, for fans. By fans, for fans.

Excel help needed


Shirohana

Recommended Posts

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?

Link to comment
Share on other sites

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?

 

Dave when you paste special, try doing it with just values and not the values and number formats.

Link to comment
Share on other sites

Dave when you paste special, try doing it with just values and not the values and number formats.

 

Thanks for your answer but that doesn't seem to work either.

After running the macro the convert to numbers option still exists, so that's why I believe that could be the problem.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...