Fill a cell on one sheet from a different sheet on the same workbook and jump to the filled sheet.

[Shady Salem]

Hi,
I was wondering if there is a code that I can use to copy multiple values of cells from a sheet to a different sheet on the same workbook and jump to it.

Example:
- If I click from range ('Original Data'!I3:O3) I want the value to go to (!Main!I10); and ('Original Data'!I3) to go to (!Main!O5), if I click from the mentioned previous range, and jump to "Main" worksheet.


Thank you.

 

[JLGWhiz]

this event code should be copied to the code module for Sheet "Original Data". The code will activate when any new cell in the range I33 is selected. It will not execute if the user clicks on the active cell in that range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Range("I3:O3"), Target) Is Nothing Then
        With Sheets("Main")
            .Range("I10") = Target.Value
            .Range("O5") = Sheets("Original Data").Range("I3").Value
        End With
    End If
Sheets("Main").Activate
Application.EnableEvents = True
End Sub

 

[Shady Salem]

Hi,

The code worked except for one small minor setback, if I click anywhere other than the defined range; it jumps to the other sheet.
I would like to sync the jump with the set range if clicked.

Thanks for the effort.

 

[JLGWhiz]

Hi,

The code worked except for one small minor setback, if I click anywhere other than the defined range; it jumps to the other sheet.
I would like to sync the jump with the set range if clicked.

Thanks for the effort.

see if this works better.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Range("I3:O3"), Target) Is Nothing Then
        With Sheets("Main")
            .Range("I10") = Target.Value
            .Range("O5") = Sheets("Original Data").Range("I3").Value
        End With
	Sheets("Main").Activate
    End If
Application.EnableEvents = True
End Sub

 

[Shady Salem]

Awesome.
Works perfectly.

I have another matter concerned with adding a formula to a command button.
Do I do a new thread for it or ask here?!

 

[Shady Salem]

Hi,
I'm sorry, I have a better idea, please disregard the previous question.

Say if I want to add the following equation =(G10-K10-O16)/2 to the previous mentioned range, whenever clicked to make the calculation and reflect with the answer on Q10.
Is that possible?

Thanks in advance.

 

[JLGWhiz]

Hi,
I'm sorry, I have a better idea, please disregard the previous question.

Say if I want to add the following equation =(G10-K10-O16)/2 to the previous mentioned range, whenever clicked to make the calculation and reflect with the answer on Q10.
Is that possible?

Thanks in advance.

Why not just put this in Q10?

=If(I10>0,(G10-K10-O16)/2,"")

 

[Shady Salem]

Because I will later on add different ranges than from I3:O3, every set of new range will have it's own equation, I want all the answers to be on the same cell Q10.
So the equation has to be linked with the range, so whenever it's clicked; the equation would be activated.

 

[Shady Salem]

Adding:
All ranges are on the "Original Data" sheet.
Cell Q10 is on the "Main".

 

[JLGWhiz]

Adding:
All ranges are on the "Original Data" sheet.
Cell Q10 is on the "Main".

You lost me. I suggest you start a new thread with a better explanation of what your objective is. Be sure to show the sheet names or number for the ranges that you reference. Remember, the responders cannot see your work unless you post copies of it or links to it.