Adjustment to Macro for formula instead of data

I have a macro that takes data/content from a cell when it changes and moves it to another WS.  Fairly simple.  BUT, the problem I have is that if I have a formula in the cell (B5), it will not pass the change over.

Thank you in advance.  B.

Private Sub Worksheet_Change(ByVal Target As Range)
       Application.ScreenUpdating = False
Select Case Target.Address
    Case "UseCase"
       End Select
       On Error GoTo 0
    Application.ScreenUpdating = True
'    End Select
If Not Intersect(Target, Range("B5")) Is Nothing Then
            Sheets("Financial Business Case").Range("e40") = Target.Value
        End If
End Sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sheets("Financial Business Case").Range("e40").Formula = Target.Formula

Pls try to replace
Sheets("Financial Business Case").Range("e40") = Target.Value


Sheets("Financial Business Case").Range("e40").Formula = Target.Formula


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Rgonzo and Kimputer,

Great work but one problem.  When I added the line, it transfers the formula.  I just want the result transferred.....not the formula in B5.  

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Rob HensonFinance AnalystCommented:
I take it you mean when the formula result in B5 changes, you want the new result to move.

I suspect this isn't happening at the minute because the Change Event is getting triggered but because the Target cell that has changed to impact the formula was not B5 then the move isn't getting triggered.

If you go into B5, press F2 and then Enter, does the move then occur?

Rob H
Bright01Author Commented:

Greetings.  YES.  If I go to B5 and press F2, it moves the formula to E40.  And you are correct.  I'm trying to move the result of the formula in B5, not the formula itself.

Rob HensonFinance AnalystCommented:
So the line in the VBA script needs to end .Value as previously written.

However, I suspect that you need to change the target cell for invoking the move, not the cell that includes the formula but at least one of the cells that feed the formula instead. Then if one of the inputs to the formula changes the result of the formula will move to the other sheet.

Is there a reason why the cell on the destination sheet isn't just a formula linking back to the cell on the source sheet?
Bright01Author Commented:
Yes.  So what this model does is there is a formula in B5:B10 that as you select a sentence on another WS, it places the selection in the cell (B5:B10).  So I'm simply trying to take the result and move it into another WS, "Financial Business Case".Range("e40") = Target.Value.  Through e40:e45.

Make sense?

If I change

Sheets("Financial Business Case").Range("e40").Formula = Target.Formula
Sheets("Financial Business Case").Range("e40").Formula = Target.Value

Will that do the trick?

Bright01Author Commented:
Thanks it to work......

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.