+ Reply to Thread
Results 1 to 2 of 2

Two-Way Link between Cells (but trying to make it four-way)

Hybrid View

  1. #1
    Registered User
    Join Date
    07-07-2014
    Location
    Brooklyn, NY
    MS-Off Ver
    2010
    Posts
    1

    Question Two-Way Link between Cells (but trying to make it four-way)

    I have four sheets where the information is identical but arranged differently. I have two columns of 135 cells each within each of those sheets that it would be useful to have link to each other, so that any entry or change in one of those cells will link to the corresponding cell in each of the three other sheets. I found this code for doing this between two sheets:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rFrom as Range
    
        If Target.Count = 1 Then
            With Target.WorkSheet
                 Set rFrom = .Range("A1:A20")
                 If Not Intersect(Target, rFrom) Is Nothing Then
                     Application.EnableEvents = False
                     'Include next line Just in Case something happens
                     '    You don't want to leave EnableEvents off
                     On Error Resume Next
                     rFrom.Copy Worksheets("Sheet2").Range("B10:B30")
                     If Err.Number <> 0 Then
                         Msgbox "Error Occurred"
                     End If
                     Application.EnableEvents = True
                 End If
             End With
        End If
    End Sub
    And it looks like it will work, but I need to be able to make it work across four different sheets. Would someone please advise me on the changes I'd need to make/if this is even worth it/possible?

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Two-Way Link between Cells (but trying to make it four-way)

    The following code when pasted in the ThisWorkbook module keeps all sheets in a workbook in sync

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    For Each Cell In Target
        For Each Sheet In Sheets
            Sheet.Range(Cell.Address).Value = Cell.Value
        Next Sheet
    Next Cell
    Application.EnableEvents = True
    End Sub
    You may need to adapt this to restrict the range in which it operates.
    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-28-2014, 02:45 PM
  2. Replies: 7
    Last Post: 04-21-2012, 08:53 PM
  3. How do I make a column of cells link to a row of data
    By Durgan Smalls in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2011, 05:36 PM
  4. can you help by make link with tow worksheet
    By nader in forum Excel General
    Replies: 1
    Last Post: 07-01-2005, 05:05 PM
  5. Can I link cells to a reference table I make?
    By EZimm in forum Excel General
    Replies: 1
    Last Post: 05-04-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1