VBA coding for pasting a formula
 

Is it possible to get a formula into the clipboard so that a pastespecial VBA
command can paste it into a cell or range of cells? If so what is the VBA
line code and format to set it? Also is it possible to retrieve the name of
the sheet/book so that a macro will run in the current book as opposed to the
book it was created in?

VBA coding for pasting a formula
 

You can put any string into the clipboard using the MSForm's
DataObject object. First, in VBA, go to the Tools menu, choose
References, and scroll down to "Microsoft Forms 2.0 Object Library"
and check the box next to it. Then, you can use code like

Dim DataObj As New MSForms.DataObject
DataObj.SetText Range("A1").Formula
DataObj.PutInClipboard

This will put the formula in cell A1 into the Clipboard. See
http://www.cpearson.com/excel/clipboard.aspx for more information
about working with the clipboard, including storing multiple items on
the clipboard.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 24 Dec 2008 13:11:01 -0800, vtj
wrote:

Is it possible to get a formula into the clipboard so that a pastespecial VBA
command can paste it into a cell or range of cells? If so what is the VBA
line code and format to set it? Also is it possible to retrieve the name of
the sheet/book so that a macro will run in the current book as opposed to the
book it was created in?