Multi-sheet variable switching

[elevenaminla]

Hi everyone,

I was wondering if I could link cells on different sheets that when changed on one sheet, changes linked ones on others?

e.g. I type "hello" into Sheet1 A1, and Sheet2 A1 changes to "hello"

or I type "hello" into Sheet2 A1, and Sheet1 A1 changes to "hello"

Can I do that?

Thanks!

 

[Smitty]

Not with a formula, but you could use a change event for each sheet:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A1")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            Sheets("Sheet2").Range(Target.Address).Value = Target.Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[elevenaminla]

Smitty, thanks for the reply! Although, I guess I should have mentioned without VBA as I don't know how to use it yet

 

[shg]

It can't be done without VBA.

 

[elevenaminla]

Ok, thanks again guys!

 

[Smitty]

I guess I should have mentioned without VBA as I don't know how to use it yet

No worries, you'll learn )), which is good because as shg pointed out, it's not possilble without it.

Just right-click on the worksheet tab, select the View Code option and paste the code in the new window that opens on the right. ALT+Q will exit you back to Excel to test it.

In this case you'll want to put the code I posted in the Sheet 1 module, then copy it to the Sheet 2 module and replace the sheet reference so it points back to sheet 1. Then any change you make to A1 on either one of those sheets will reflect back to the other.

 

[elevenaminla]

Ha! Dude, amazing! It works and was SO easy. Thank you so, so, so much!

 

[elevenaminla]

Hey guys, how would I do this to make it change across multiple sheets not just between 2? I tried the following, but it didn't work:


Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("D1")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
Sheets("P&L", "Revenues", "Cash", "Costs", "Employees").Range(Target.Address).Value = Target.Value
End Sub

I replaced the Sheets with the other sheets in this workbook... and likewise on each sheet accounted for the others.

 

[pgc01]

Hi

Try this example and then adapt to your case.

Open a new workbook with 5 worksheets (Sheet1, ..., Sheet5)

If you write a value in D1 in one of the sheets (Sheet1, Sheet2, Sheet3) you want that value to be copied to the other 2.

Paste in the worksheet modules of those 3 sheets:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
 
Dim rng As Range
 
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
 
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Me.Range("D1")
 
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
 
' Action if Condition(s) are met (do your thing here...)
Application.EnableEvents = False
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).FillAcrossSheets Target
Application.EnableEvents = True
 
End Sub

Remark: this assumes you just want this in a set of sheets in the workbook. If you'd like it in all the sheets, it would be better to use the corresponding workbook Sheet_Change event, that deals with all the worksheets at a time.

 

[elevenaminla]

pgc01,

Thanks for the code.. I tried implementing it on the 3 sheets I want to use this on, replacing "Sheet1" with "Costs", etc, but it didn't seem to work.

Is the code you show exactly what I need, or do I leave out the current sheet's name in the code ie if I'm on Sheet1, do I leave it out of the Sheets(Array() list?

Thanks