Paste special

[huorsa]

I want to know if there is a shortcut for paste special, because I just want to copy the formulas, without formats. Selecting in the menu paste special and then formulas is the usual way and I am looking for a quicker way.

Thanks,

Hugo

 

[Joe Was]

This code will work with a hot-key that you assign with Macro-Macros, select macro name, Options, then add your letter key.

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

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

Note: the code above includes two other paste opions that are commented out!

You select the cell to copy hit copy then select the cell to paste to, then hit your hot-key. JSW

_________________<INPUT type="JSW" value=" Excel On... Coder's!" ID=Text1><spanstyle='font-size:36.0pt;color:#FFCC00'>JSW<o></o></span>[/b]</p><spanstyle='font-size:36.0pt;color:#FFCC00'>Try and try again, " The way of the coder!"<o></o></span>[/b]
This message was edited by Joe Was on 2002-10-04 14:29

 

[rboi]

I read your macro that on mr. excel to help with the paste special function.
I have two questions on it. How can I use this as a general function for
all my workbooks and how can I get it to apply one cell to many. I am using
it for the paste special formats function so I deleted out the comments and
added them to the formulas line so that it would work, however I cannot get
it to work in all my workbooks and over a range of cells. I would
appreciate it if you could help.

Here is what you wrote.

This code will work with a hot-key that you assign with Macro-Macros, select
macro name, Options, then add your letter key.

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

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

Note: the code above includes two other paste opions that are commented out!


You select the cell to copy hit copy then select the cell to paste to, then
hit your hot-key. JSW

 

[shades]

I read your macro that on mr. excel to help with the paste special function.
I have two questions on it. How can I use this as a general function for
all my workbooks and how can I get it to apply one cell to many. I am using it for the paste special formats function so I deleted out the comments and added them to the formulas line so that it would work, however I cannot get it to work in all my workbooks and over a range of cells. I would appreciate it if you could help.

This code should be pasted into a module in Personal.xls. Then it will be available at all times.

 

[Joe4]

If you save the code to your Personal workbook, it will always be available in Excel. The easiest way to do this is to:

1. Record a new macro (use the Marco Recorder), selecting to "Store Macro In Personal Macro Workbook".

2. Stop the macro recorder.

3. Go into the VB editor, find the "Personal.xls" workbook, open the module.

4. Copy your "Paste Special" code and paste it into the module in the Personal.xls workbook.

5. Save the file.

Now whenever Excel is open, this macro should be available to you.

 

[rboi]

Thanks, now how can I get the macro to work if I copy one cell and try to paste the formats across multiple cells. I cannot get that to work. Any ideas??