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
Select Case Target.Address
Case "UseCase"
End Select
On Error GoTo 0
Application.ScreenUpdating
' End Select
If Not Intersect(Target, Range("B5")) Is Nothing Then
Sheets("Financial Business Case").Range("e40") = Target.Value
End If
End Sub
Thank you in advance. B.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating
Select Case Target.Address
Case "UseCase"
End Select
On Error GoTo 0
Application.ScreenUpdating
' End Select
If Not Intersect(Target, Range("B5")) Is Nothing Then
Sheets("Financial Business Case").Range("e40") = Target.Value
End If
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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?
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?
ASKER
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").Formul
to
Sheets("Financial Business Case").Range("e40").Formul
Will that do the trick?
B.
Make sense?
If I change
Sheets("Financial Business Case").Range("e40").Formul
to
Sheets("Financial Business Case").Range("e40").Formul
Will that do the trick?
B.
ASKER
Thanks guys.....got it to work......
B.
B.
ASKER
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.