Two way updating of two different sheets in two different workbooks

[chazrab]

As stated. Specifically - two way updating of Sheet1 of wkbk 1 and Sheet2 of wkbk2?. That is, it makes no difference which workbook you're in -= any change made to either workbook will always ensure both sheets have exactly the same data at all times ? BTW: both workbooks are saved to OneDrive at different times constantly during the day. I know I can do this already in cell A1 of wkbk1:

=[wkbk2.xlsm $A$1]

[code]


Thx for anyone's help.
cr

 

[Jeffrey Mahoney]

Not easy!
* If 2 users have WB1 and WB2 open and are making changes at the same time: which entries have higher priority?
* If WB1 is being edited, but not saved; then WB2 is opened and compares the values to WB1 (unsaved): WB2 will be wrong.

* You would have to prevent both WBs from being edited at the same time.
* A Master WB would keep the latest values and opened every time either of the other 2 WBs are opened. This would remain hidden and in the background
* When a user opens either WB, a macro would have check if the other Master WB is open: If it is open, then the WB remains read only
* Before a WB is closed, all the values would be copied to the Master WB and both are closed.

I welcome others to chime in if they have a different approach.

Jeff

 

[Skyybot]

Maybe Collaborating? Not quite sure if that's what you're looking for. I may be way off base here.

Collaborate on Excel workbooks at the same time with co-authoring - Microsoft Support

You can open an Excel workbook and collaborate with others at the same time using co-authoring. This article will explain how.

support.microsoft.com

 

[chazrab]

Not easy!
* If 2 users have WB1 and WB2 open and are making changes at the same time: which entries have higher priority?
* If WB1 is being edited, but not saved; then WB2 is opened and compares the values to WB1 (unsaved): WB2 will be wrong.

* You would have to prevent both WBs from being edited at the same time.
* A Master WB would keep the latest values and opened every time either of the other 2 WBs are opened. This would remain hidden and in the background
* When a user opens either WB, a macro would have check if the other Master WB is open: If it is open, then the WB remains read only
* Before a WB is closed, all the values would be copied to the Master WB and both are closed.

I welcome others to chime in if they have a different approach.

Jeff

Hi Jeff - thx for replying. I thought I'd never get an answer - just to clarify - there will never be more than 1 user - just me. What I have is now Sheet1 of wkbk1 and Sheet2 of wkbk2.
I'm constantly back and forth working in both workbooks all day long. The sheet structure in both wkbks are identical.

The sheets are the data in an underlying address book with the data displayed and changes made in a custom userform.

When I add a new record or a change is made in any of the textboxes in the userform in wkbki or wkb2 and changes made to the underlying sheet, the changes or saved to OneDrive and Dropbox.

The same process for wkbk2. What I'm doing now is just copying the entire sheets and pasting to the other wkbk, and renaming it and vv when any change is made.. This is a crude way to make both Sheets exactly the same all the time both wkbks - .

I hope I made this clearer.

Thx for all your help.

cr

 

[chazrab]

Maybe Collaborating? Not quite sure if that's what you're looking for. I may be way off base here.

Collaborate on Excel workbooks at the same time with co-authoring - Microsoft Support

You can open an Excel workbook and collaborate with others at the same time using co-authoring. This article will explain how.

support.microsoft.com

Hi skybot - thks also for replying. See my reply to Jeff.

 

[Skyybot]

there will never be more than 1 user

OH. Then my suggestion won't work. I will some with something.

 

[Skyybot]

I'm curious to know why you need two identical workbooks. If they are the same, why not just have one workbook?

 

[Jeffrey Mahoney]

I would alter my response above like this. One of those workbooks that you work in needs to be the master. The other workbook needs to reference the master in terms of what kind of data it displays and where it saves its data.

* Workbook one is Master data
* Workbook 2 has menus just like workbook one to be able to see the information but it only saves to the master.

You can create either a user form with macros to be able to save to the master or a user form in a sheet same thing

 

[chazrab]

I'm curious to know why you need two identical workbooks. If they are the same, why not just have one workbook?

The workbooks are not identical. The sheets in each workbook that store the data must be identical for this particular userform.
Each workbook contains many different userforms with many different underlying sheets. Only those two are theo ones that need to
be exactly the same as mentioned earlier.

cr

 

[Trebor76]

Hi Charles,

What about having an event macro on each workbook that runs each time the workbook is selected (activated) i.e. for wkbk1 it would be...

Option Explicit
Private Sub Workbook_Activate()

    'This code would go on the 'ThisWorkbook' object not a standard module
    'It is for 'wkbk1'

    Dim wbCopyFrom As Workbook
    Dim wsCopyFrom As Worksheet, wsCopyTo As Worksheet
    Dim lngStartRow As Long, lngLastRow As Long
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    lngStartRow = 2 'Row where data starts in both files. Change to suit.
    
    On Error Resume Next
        Set wbCopyFrom = Workbooks("wkbk2.xlsb") 'Name and extension of workbook to copy in. Change to suit.
        If Err.Number = 0 Then
            Set wsCopyFrom = wbCopyFrom.Sheets("Sheet2") 'Sheet name in 'wkbk2' to copy in. Change to suit.
            If Not wsCopyFrom Is Nothing Then
                With ThisWorkbook.Sheets("Sheet1") 'Sheet name in this workbook to copy the data into. Change to suit.
                    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    If lngLastRow >= lngStartRow Then
                        .Rows(lngStartRow & ":" & lngLastRow).ClearContents
                    End If
                    lngLastRow = wsCopyFrom.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    wsCopyFrom.Rows(lngStartRow & ":" & lngLastRow).Copy Destination:=.Range("A" & lngStartRow)
                End With
            End If
        End If
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
                
End Sub

...and the reverse for wkbk2:

Option Explicit
Private Sub Workbook_Activate()

    'This code would go on the 'ThisWorkbook' object not a standard module
    'It is for 'wkbk2'

    Dim wbCopyFrom As Workbook
    Dim wsCopyFrom As Worksheet, wsCopyTo As Worksheet
    Dim lngStartRow As Long, lngLastRow As Long
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    lngStartRow = 2 'Row where data starts in both files. Change to suit.
    
    On Error Resume Next
        Set wbCopyFrom = Workbooks("wkbk1.xlsb") 'Name and extension of workbook to copy in. Change to suit.
        If Err.Number = 0 Then
            Set wsCopyFrom = wbCopyFrom.Sheets("Sheet1") 'Sheet name in 'wkbk2' to copy in. Change to suit.
            If Not wsCopyFrom Is Nothing Then
                With ThisWorkbook.Sheets("Sheet2") 'Sheet name in this workbook to copy the data into. Change to suit.
                    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    If lngLastRow >= lngStartRow Then
                        .Rows(lngStartRow & ":" & lngLastRow).ClearContents
                    End If
                    lngLastRow = wsCopyFrom.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    wsCopyFrom.Rows(lngStartRow & ":" & lngLastRow).Copy Destination:=.Range("A" & lngStartRow)
                End With
            End If
        End If
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
                
End Sub

Note the location of each macro goes in the ThisWorkbook object of each workbook (refer attached) not a standard module.

Hope that helps.

Regards,

Robert