VBA Sync cells over multiple workbooks

[newbie22922792]

Hello everyone,

I have a special request that I cant solve on my own. I searched all over the internet but could not find anything. I really want to prusue my goal because I am an intern at my company.

I have two documents ("workbooks"). One is called "Doc1", the other one is called "Doc2". Both documents comprise of one table "Table1".
I want to sync the cells A1 between Doc1_Table1 and Doc2_Table1. This needs to work in both ways, e.g. if I make changes in Doc1_Table 1 cell A1, it should sync to Doc2_Table1 cell A1 and vice versa.
I already found a way to sync cells from different tables WITHIN one document. But don't know how to change the code to sync over two documents.

WITHIN the docum

Your help is highly appreciated! Thank you very much!

 

[newbie22922792]

Hello everyone,

I have a special request that I cant solve on my own. I searched all over the internet but could not find anything. I really want to prusue my goal because I am an intern at my company.

I have two documents ("workbooks"). One is called "Doc1", the other one is called "Doc2". Both documents comprise of one table "Table1".
I want to sync the cells A1 between Doc1_Table1 and Doc2_Table1. This needs to work in both ways, e.g. if I make changes in Doc1_Table 1 cell A1, it should sync to Doc2_Table1 cell A1 and vice versa.
I already found a way to sync cells from different tables WITHIN one document. But don't know how to change the code to sync over two documents.

WITHIN the document I can sync two cells over different tables (e.g. table1 & table2) like that:

(this is what i put into table1 to connect to table 2 - and in table two i have the same but replaced for table1)

Private Sub Worksheet_Change (ByVal Target As Range)
If Intersect (Taget, Range ("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Sheets("Table2") .Range("A1") = Target
Application.EnableEvents = True
End Sub

now i need a smiliar code but OVER the two documents.. please help!

Your help is highly appreciated! Thank you very much!

 

[mumps]

First you have to make sure that both workbooks are open and then you have to fully define the destination range to include the workbook name.

Workbooks("Doc2.xlsx").Sheets("Table1").Range("A1") = Target

 

[newbie22922792]

Many thanks!! It is still not working like that i don't know why

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your 2 sheets. Better still, you could upload a copy of your files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[newbie22922792]

Hello! Now it worked as you described! Thank you so much! I just don't know how I can add other cells to the code. I too want to sync e.g. A1 (doc1) with A1(doc2) or e.g. B2 (doc1) with B3 (doc2)

maybe you can tell me how to add that. Thanks!

Private Sub Worksheet_Change (ByVal Target As Range)
If Intersect (Taget, Range ("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Sheets("Table2") .Range("A1") = Target
Application.EnableEvents = True
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sync cells over multiple workbooks with VBA
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mumps]

B2 (doc1) with B3 (doc2)

Did you mean : A2 (doc1) with A2 (doc2) ?

 

[newbie22922792]

I want to sync multiple cells with oneanother. e.g. cell A1 (doc1/table1), A2(doc2/table1) and A3(doc2/table2)

 

[mumps]

This macro in the code module for Doc1, Table1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column = 1 Then
        Application.EnableEvents = False
        Workbooks("Doc2.xlsx").Sheets("Table1").Range("A" & Target.Row) = Target
        Application.EnableEvents = True
    End If
End Sub

This macro in the code module for Doc2, Table1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column = 1 Then
        Application.EnableEvents = False
        Workbooks("Doc1.xlsx").Sheets("Table1").Range("A" & Target.Row) = Target
        Application.EnableEvents = True
    End If
End Sub