VBA / Macro help needed to auto update the data from another workbook

Hi Folks,

I have two workbooks first one is called "JobList.xlsm" and second one is "Workpack_Status_Tracker.xlsm". And, these are handling by two separate users.

I need a Macro to update some columns from "JobList.xlsm" to "Workpack_Status_Tracker.xlsm" and one column from "Workpack_Status_Tracker.xlsm" to "JobList.xlsm", if any changes made in the workbooks. Also I have a "Summary" sheet in "Workpack_Status_Tracker.xlsm" these also need to updated (details marked in the sheet).

I have attached an excel file of what I'm trying to accomplish. Thanks in advance for your expertise!

Joshi

How would you identify if any changes are done to either files?

Also, where are these 2 files saved? On a shared drive?

Hi Arlu,
While opening any file its should be auto updated. Both files saved in a share drive "G:\sd\workpack status"

Thanks for you time,
Joshi

So is it fine if the existing data is cleared and then fresh data input? Otherwise there would be duplicates.

Yes, it's should be like that, no duplicate record.

Thanks,
Joshi

So the columns that you have highlighted and put comments should be copied to each workbook? If it all happens on a cell change, which is the column where the data will be updated?

And if data in workbook A needs to be copied to data in workbook B, and then in reverse order, it happens at 2 different times right?

Hi Arulu,

Yes you are right, if any changes done or new records added in JobList.xlsm then Column "A to P" to be copied to Workpack_Status_Tracker.xlsm. And if any changes done in Workpack_Status_Tracker.xlsm "Q" Column, this column ("Q") only to be copied to JobList.xlsm workbook.

Thanks,
Joshi

But would the rows match ? Should any unique column be checked before the data is copy pasted between the sheets?

Arlu,

Yes, the row should be match. There is a column "G" "Sd Job No" that's the unique column for the row match.

Thanks for your time and help.

Arlu,

Sorry to bother you, please help me.

Hi, krjoshi,

you stated

if any changes made in the workbooks.

As you stated as well that both workbooks will have different persons working on them could you please explain if both workbooks are open in each users instance of Excel or just get opened for a change and get closed thereafter? From what I understand we should offer a solution for two workbooks in two separate instances of excel and two different users which should update each other. And right now I only have a glimpse of an idea as to work with textfiles to get that done if the workbooks are open "all day long" by each user. If the workbooks are only used for changing or appending data either the Workbook_Open or Workbook_BeforeClose event might be used to update any workbook.

The data you supplied is starting in 2011 - will there be all data inside the workbooks or do you plan to move the done cases to a history (if so - do it manually or have a time span for an automatic)?

Ciao,
Holger

Hi Holger,

Thanks for your reply. Please note that the some times the both workbooks will open by both user and update their data at a time, some times they will close the files immediately after update and some time they keep open long time for long update. And we cannot insist them to do update one after one. For the date I supplied from 2011 please ignore it (By Mistake I entered), the summary should begin with =MIN () and =MAX of Column "S" in workbook "Workpack_Status_Tracker.xlsm"

Hi, krjoshi,

when I first read your request I thought that I would use a RDBMS like Access instead of Excel for this. As for the update it is mandatory to use VBA we could implement an automatic closing of any workbook after a given time (say 5 minutes) if no action has been taken in the workbook. Question to be answered there would be close and save changes or not. I think IŽll take a walk in the rain to get my mind clear about how to solve this. IŽll be back later.

Ciao,
Holger

Hi, Joshi,

having thought about it a bit longer I may categorize 3 different situations.

First:
working on both files on the same computer in the same instance. Hardly a challenge as the following codes may be used:

Private Sub Worksheet_Change(ByVal Target As Range)
'behind WP_Tracker
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngFound As Range

If Target.Count > 1 Then Exit Sub

If Target.Row > 1 And Target.Column < 18 Then
  
  On Error Resume Next
  Set wkb = Workbooks.Open(ThisWorkbook.Path & "\JobList.xlsm")
  On Error GoTo 0
  
  If Not wkb Is Nothing Then
    Set wks = wkb.Sheets("Justified")
    Set rngFound = wks.Range("G:G").Find(what:=Cells(Target.Row, "G").Value)
    
    If Not rngFound Is Nothing Then
      Application.EnableEvents = False
      wks.Range("A" & rngFound.Row & ":P" & rngFound.Row).Value = Range("A" & Target.Row & ":P" & rngFound.Row).Value
      wks.Range("Q" & rngFound.Row).Value = Range("Q" & rngFound.Row).Value
      Application.EnableEvents = True
    End If
    
    Set wks = Nothing
    wkb.Close True
    Set wkb = Nothing
  End If
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'behind Justified
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngFound As Range

If Target.Count > 1 Then Exit Sub

If Target.Row > 1 And Target.Column < 17 Then
  
  On Error Resume Next
  Set wkb = Workbooks.Open(ThisWorkbook.Path & "\Workpack_Status_Tracker.xlsm")
  On Error GoTo 0
  
  If Not wkb Is Nothing Then
    Set wks = wkb.Sheets("WP_Tracker")
    Set rngFound = wks.Range("G:G").Find(what:=Cells(Target.Row, "G").Value)
    
    If Not rngFound Is Nothing Then
      Application.EnableEvents = False
      wks.Range("A" & rngFound.Row & ":P" & rngFound.Row).Value = Range("A" & Target.Row & ":P" & rngFound.Row).Value
      Range("Q" & rngFound.Row).Value = wks.Range("Q" & rngFound.Row).Value
      Application.EnableEvents = True
    End If
    
    Set wks = Nothing
    wkb.Close True
    Set wkb = Nothing
  End If
End If

End Sub

Second:
working on the file in two instances of Excel on the same computer. As no direct connection is available copying between the files could be used.

Third:
working with 2 files on two different computers. No shared workbook could be used for this as you are working with 2 different workbooks. And any VBA code will have to look if the other workbook is open and then act according to the first code or use either a textfile or a third workbook to store the data. This could be done by checking if any entry/change has been made to import the available new data.

This overhead may lead to problems if both workbooks update in the same area at about the same time. Apart from that there is a multiple storing of the same data.

That was the reason why I stated that I would have used a Database. Most of the action in Excel by programming can be done in a Database with less or hardly any programming, thus the DB having better algorithms for record locking, storage of data, security. Excel may be used as a frontend to the Database.

Ciao,
Holger