Paste special

huorsa

Board Regular
Joined
Feb 15, 2002
Messages
101
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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:p></o:p></span>[/b]</p><spanstyle='font-size:36.0pt;color:#FFCC00'>Try and try again, " The way of the coder!"<o:p></o:p></span>[/b]
This message was edited by Joe Was on 2002-10-04 14:29
 

rboi

New Member
Joined
Nov 21, 2002
Messages
10
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

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
rboi said:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,396
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 21, 2002
Messages
10
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??
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top