[VBA EXPERTS NEEDED] Mirroring columns to two different sheets

gomgom2

New Member
Joined
Jan 12, 2018
Messages
7
I just created a thread but seems its gone.. so re-writing.

What I am trying to do is make a range which will be mirrored in two different sheets.

That is, they accept input which will be reflected in either of the range I specify.

For example, if I put 123123 on Range1, it will be shown the same on range 2.

Also, When I change to 11111 in rage2, it will automatically update range1.

For Sheet1


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Set r1 = Sheet1.Range("Range1")
    Set r2 = Sheet2.Range("Range2")
    If Intersect(Target, r1) Is Nothing Then Exit Sub
        
        r2.Value = r1.Value
    
End Sub

For Sheet2,

To avoid the loop
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Dim c As Range
    Set r1 = Sheet2.Range("vslname2")
    Set r2 = Sheet1.Range("vslname1")


    If Intersect(Target, r1) Is Nothing Then Exit Sub
    If r2.value<>r1.value then
        r2.Value = r1.Value

    End If
End Sub

Please help!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to the forum!

You didn't tell us what you are having problems with, but to avoid going into a loop you would temporarily disable events; one way of re-writing your code to handle that could be something like.

Sheet1
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Sheet1.Range("Range1")
Set r2 = Sheet2.Range("Range2")
If Not Intersect(Target, r1) Is Nothing Then
  Application.EnableEvents = False
  r2.Value = r1.Value
  Application.EnableEvents = True
End If
End Sub

Sheet2
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Sheet1.Range("Range1")
Set r2 = Sheet2.Range("Range2")
If Not Intersect(Target, r2) Is Nothing Then
  Application.EnableEvents = False
  r1.Value = r2.Value
  Application.EnableEvents = True
End If
End Sub
 

gomgom2

New Member
Joined
Jan 12, 2018
Messages
7
Thank you very much for the replies. This site is just amazing. I wasn't even expecting a feedback.

May be I was too disappointed after my first writing being deleted. Let me write it in more details.

1. What I am trying to do is linking both ranges so that I can work on either of the sheets.

2. I cannot afford disabling events, since I already have other codes that will be affected by this.

3. Special Thanks to Johnny C, I have looked into all the replies you made earlier in the previous thread. But somehow it is whole another level making it to the range rather than just one destined cell.

Please help make this work....... :( thank you.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

2. I cannot afford disabling events, since I already have other codes that will be affected by this.

Hi, this is temporary and only occurs whilst the sheets are updated - events are immediately turned back on automatically within the code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,236
Office Version
  1. 365
Platform
  1. Windows
@gomgom2
May be I was too disappointed after my first writing being deleted. Let me write it in more details.
Your original post was not deleted, it simply required moderator approval & you should have received a message to that affect. In future please wait for posts to be approved, rather than re-posting.
Cheers
 
Last edited:

gomgom2

New Member
Joined
Jan 12, 2018
Messages
7
Hi, this is temporary and only occurs whilst the sheets are updated - events are immediately turned back on automatically within the code.

I do understand it is temporary. However, I was wondering if there is any other way to make it work rather than disabling and enabling again.

To be more specific, I have made a switch to turn on and off the event to be able to use some features selectively. By using this code, it will always be turned on...
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, you posted some code in post # 1, but you haven't told us in what way it doesn't work - maybe that would be a good starting place?

I have made a switch to turn on and off the event to be able to use some features selectively. By using this code, it will always be turned on...

If events have been disabled by your switch then the change event won't fire and it won't turn them back on again :confused:
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,198
Messages
5,600,280
Members
414,374
Latest member
akbir

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