Automatically Copy Formula Range On Change & Paste Values

lablab · Oct 26th 2008

Hi!

How can I do the following?

I want to automate the following steps when cell A8:A11 changes in sheet "InfoAA":

(1) clear contents and formats of cells A1:A4 in sheet "InfoBB"
(2) copy cells A8:A11 of sheet "InfoAA" (which are formulas) and past it as text in cells A1:A4 of sheet "InfoBB".
(3) then automatically run a recorded macro named "BoldFirstName"

[Blocked Image: http://img148.imageshack.us/img148/1772/preferredfruitstep2cs1.jpg]

See attachment also if needed.

Thanks.

jhenderson · Oct 26th 2008

Re: Auto Copy Formula Results As Text

Use The Macro Recorder

lablab · Oct 26th 2008

Re: Auto Copy Formula Results As Text

Thanks... but I'd rather have VB coding!

Dave Hawley · Oct 26th 2008

Re: Auto Copy Formula Results As Text

Right click on the InfoAA sheet name tab, choose View Code and use;

Code

Private Sub Worksheet_Calculate()
    Range("A8:A11").Copy
    Sheets("InfoBB").Range("A1").PasteSpecial xlValues
    Application.CutCopyMode = False
    Run "BoldFirstName"
End Sub

lablab · Oct 27th 2008

Re: Automatically Copy Formula Range On Change & Paste Values

Dave... I tried the code but I get the following error:

Run-time Error '1004':
Applicatio defined or object defined error.

Did I enter something wrong?:read:

Dave Hawley · Oct 27th 2008

Re: Automatically Copy Formula Range On Change & Paste Values

My bad, all fixed, try it now.

lablab · Oct 27th 2008

Re: Automatically Copy Formula Range On Change & Paste Values

Thanks! It works!

Automatically Copy Formula Range On Change & Paste Values

Participate now!

Share