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

Shady Salem

New Member
Joined
Mar 13, 2016
Messages
9
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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 I3:eek:3 is selected. It will not execute if the user clicks on the active cell in that range.

Code:
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
 
Last edited:

Shady Salem

New Member
Joined
Mar 13, 2016
Messages
9
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Mar 13, 2016
Messages
9

ADVERTISEMENT

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

New Member
Joined
Mar 13, 2016
Messages
9
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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?
Code:
=If(I10>0,(G10-K10-O16)/2,"")
 
Last edited:

Shady Salem

New Member
Joined
Mar 13, 2016
Messages
9
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.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,313
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top