MrExcel Publishing
Your One Stop for Excel Tips & Solutions

ecxel macro paste special

Posted by Jochen Weber on July 11, 2001 11:20 AM


J have to write two macro (VSB)in Excel 97.

1. Paste special (Special_All/Formats?Values))
2. Sort Ascending/Descending

I would be very happy if somebode could help me.


Posted by Joe Was on July 11, 2001 12:56 PM

Sub sPaste()
'This code pastes the format and value of the current selection,
'to the active range. The option has been set to Hot-key Ctrl-s

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

This code will take the current copy selection and paste it to the active range. You must set the Hot-key (Tools-Macro-Macros-Options assign key).

Number two has been answered before. Use Ctrl-f to bring-up the search utility and surch the BB and Ark's for: Sort.

Or record your own macro (Tools-Macros-Record macro) then use the menu to use the sort utility. Stop the recorder (press the square button) then, (Tools-Macro-Macros-Edit) remove the specific cell-range references and replace them with new generic range selection code or "ActiveCell.Select." JSW