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
Bright01Asked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

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

with

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

Regards
0
 
KimputerCommented:
Sheets("Financial Business Case").Range("e40").Formula = Target.Formula
0
 
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.  

B.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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?

Thanks
Rob H
0
 
Bright01Author Commented:
Rob,

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.

b.
0
 
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?
0
 
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
to
Sheets("Financial Business Case").Range("e40").Formula = Target.Value

Will that do the trick?

B.
0
 
Bright01Author Commented:
Thanks guys.....got it to work......

B.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.