Editing cell in multiple tab WITHOUT grouping

cowboysnut

New Member
Joined
Sep 22, 2015
Messages
14
Is there a way to edit cell M2 on Sheet1 where it updates on Sheet2 as well, but if I edit it on Sheet2 it updates it on Sheet1 WITHOUT grouping or selecting the tabs? Can I somehow specify that a certain cell is live and active through all sheets?

I currently have an equation on all sheets that ties back to my home indexing sheet and I update it there, but I would like to cut out the step of jumping back to the home sheet if I can.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Is there a way to edit cell M2 on Sheet1 where it updates on Sheet2 as well, but if I edit it on Sheet2 it updates it on Sheet1 WITHOUT grouping or selecting the tabs? Can I somehow specify that a certain cell is live and active through all sheets?

I currently have an equation on all sheets that ties back to my home indexing sheet and I update it there, but I would like to cut out the step of jumping back to the home sheet if I can.

If I am understanding correctly, you should be able to achieve this with some small code if it is ok for you to use Macros :

Code in the ThisWorkbook Module:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If (Sh Is Sheet1 Or Sh Is Sheet2) And Not Intersect(Target, Range("M2")) Is Nothing Then
        Application.EnableEvents = False
        Sheet1.Range("M2") = Range("M2"): Sheet2.Range("M2") = Range("M2")
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
Or better use this code :
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If (Sh Is Sheet1 Or Sh Is Sheet2) And Not Intersect(Target, Range("M2")) Is Nothing Then
        Application.EnableEvents = False
        Sheet1.Range("M2").Formula = Range("M2").Formula: Sheet2.Range("M2").Formula = Range("M2").Formula
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
That's along the right lines, but I described it a little more simply than what I actually have. It's a document with maybe 30 sheets in it so that logic would become extraordinarily bulky if I were to try using that method if I am understanding it correctly.
 
Upvote 0
That's along the right lines, but I described it a little more simply than what I actually have. It's a document with maybe 30 sheets in it so that logic would become extraordinarily bulky if I were to try using that method if I am understanding it correctly.

Do you need this to work on ALL sheets or just on 30 sheets out of all the sheets located in the workbook ? ie: Does the workbook contain 30 sheets or more ? and Is this always going to be Cell M2 for each sheet or are there other cells involved as well ?
 
Last edited:
Upvote 0
I want this over all the sheets in the document. One sheet (Index) has them in a different location (K4) while all the others will be the same location (P2). If I must, I can try to get them all at the same location but I'd prefer leaving everything where it is. The use of macros is perfectly fine. Thanks for your help.
 
Upvote 0
I want this over all the sheets in the document. One sheet (Index) has them in a different location (K4) while all the others will be the same location (P2). If I must, I can try to get them all at the same location but I'd prefer leaving everything where it is. The use of macros is perfectly fine. Thanks for your help.

See if this works for you.

In the following code, I am assuming the name of the one worksheet with the K4 location is Sheet3 . I have assigned the sheet name to the String Constant IRREGULAR_SHEET at the very start of the Change event routine so that you can amend it easily as required.


Code in the ThisWorkbook Module:
Code:
Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Const IRREGULAR_SHEET As String = "Sheet3" [B][COLOR=#008000]'<== Change this specific Sheet name as required.[/COLOR][/B]
    Dim oSh As Worksheet, oRange As Range
    
    On Error GoTo Xit
   
    Application.EnableEvents = False
    If Sh.Name = IRREGULAR_SHEET And Not Intersect(Target, Range("K4")) Is Nothing Then
        Set oRange = Range("K4")
    ElseIf Sh.Name <> IRREGULAR_SHEET And Not Intersect(Target, Range("P2")) Is Nothing Then
        Set oRange = Range("P2")
    Else
        GoTo Xit
    End If
    
    For Each oSh In Me.Worksheets
        If oSh.Name = IRREGULAR_SHEET Then
            oSh.Range("K4").Formula = oRange.Formula
        Else
            oSh.Range("P2").Formula = oRange.Formula
        End If
    Next oSh
Xit:
    Application.EnableEvents = True
End Sub
 
Upvote 0
This does not seem to be outputting anything for me. I've tried playing with the sheet names with no success. I tried it without the "ByVal Sh As Object, ByVal Target As Range" after the title so I could run the debugger and it crashed on "Me.Worksheets" I'm not too familiar with that kind of declaration but from trying to look it up it might be correct. I admit to not being the strongest on coding, but if I am following this correctly it shows a lot of potential!
 
Upvote 0
This does not seem to be outputting anything for me. I've tried playing with the sheet names with no success. I tried it without the "ByVal Sh As Object, ByVal Target As Range" after the title so I could run the debugger and it crashed on "Me.Worksheets" I'm not too familiar with that kind of declaration but from trying to look it up it might be correct. I admit to not being the strongest on coding, but if I am following this correctly it shows a lot of potential!

Can you upload a copy of your workbook (after removing any sensitibve info) to a file sharing site like Box.net or to other file sharing site and post a link here so I can take a look ?
 
Upvote 0
Can you upload a copy of your workbook (after removing any sensitibve info) to a file sharing site like Box.net or to other file sharing site and post a link here so I can take a look ?


https://drive.google.com/file/d/1WRbYfSHtxRoGUJX30n31opzkQYCCF5xp/view?usp=sharing

Download it from there. It doesn't work properly on Google's viewer. I scrubbed a lot of the file away and only created a few sheets, but right now if you are on the "Index" sheet and change the date range in K4 or L4 then all other tabs are slaves to it. My objective is to have it so if I change the date in P2 or Q2 on any of the individual tabs that all tabs would follow suit.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

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
Back
Top