Paste Formula Customized Command

[JDC]

Excel allows the customization of toolbar commands for paste values, paste
special, paste formatting, and paste as hyperlink. The only option it does
not seem to have is paste formula.

Is there a way to create a customized toolbar command with just the paste
formula option?

 

[Sheeloo]

Excel 2007 has the Paste Special|Formulas option available to add it to the
Quick Access Toolbar.

I think it is there in 2003 also. If it is not there then you can record (or
write) a macro and assign it to a button on the toolbar...

 

[JDC]

I am using Excel 2003 and that option is not available. Recording a macro
for this function and assigning it to a button is over my head. Is there a
simple set of instructions that can be provided to create one?

 

[Sheeloo]

See http://www.rondebruin.nl/personal.htm for instructions. Instructions are
simple but the concept takes a little time...

Here is the macro to use
Sub pasteSpcial_Formulas()
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Another way is to go to
Copy a range of formula you want to copy
Click on the destination Cell (single cell)
Tools->Macros->Record a macro
Choose Personal Workbook in the 'Store macro in...' option
Give the macro a name or live with the default
Do your paste special
Stop recording

Then create a button, assign the macro recorded above to the button.

It seems a lot but believe me it is worth the effort and it will save you
hours in future...

 

[rob]

This works only if you have data in the paste buffer, but will return an
error when paste buffer is empty.

Is it possible to dim the button if paste buffer is empty (just like the
other standard paste button in excel)?

regards

rob

 

[Sheeloo]

I believe it is possible however I have not done it myself.

You can add a message in case of ERROR like in the macro below

Sub pasteSpcial_Formulas()
On Error GoTo errormessage
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Exit Sub
errormessage:
MsgBox "There is nothing to paste!"
End Sub

 

[FSt1]

hi
in xl03, clicikng the paste special icon produces a pop up with a number of
paste options. paste formula only is one of them.

Regars
FSt1