Opening a workbook with VBA after a cell value is entered.

[XrayLemi]

I am trying in vain to figure this out.
I have multiple workbooks. One is a list (WB #1). The list is generated from data in other workbooks. I'm trying to get the list to auto-populate after data in a cell is entered in the other WBs. If you enter data in column H in WB #2, I want to use VBA to open WB #1 add the value from column H to WB #1, then save and close WB #1. I also want to add the same code to WB #3 and 4 etc. to do the same task.

This gives me multiple problems.

From what I gather, any code that opens or closes a workbook should? must? be in either the This workbook area or in a module. If I put the code in either of these places, I can't use sheet specific code. It also seems I can't use code to open a file from sheet specific locations. I can get parts of my code to work separately but combining the two parts is where I'm stuck.

I am sorry if I am being vague with the information I am giving you. I just want to be pointed in the right direction. I am trying hard to learn this. and I want to try and do it on my own before someone writes the entire code for me.
Thanks,
Jim

 

[Yongle]

I will post my working code in 24 hours
Lots of clues below
Enjoy the puzzle

Place code in SHEET code window so that it is triggered when cell value in column H is amended

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> Columns("H").Column Then Exit Sub
    Application.ScreenUpdating = False
    Dim Wb As Workbook, Ws As Worksheet, Cel As Range
    Const masterFilePath = "path to master"
  
'set wb to open the master workbook and set cel to th
    Set Wb = this is where you open the master workbook
    Set Ws = the correct sheet in master
    Set Cel = destination cell in next available row in Ws
'copy the corect row from sheet to the next available row in master
    copy correct range (using Target)  to Cel
'save and close master
    Wb.Close True
End Sub

Also consider ...
- how to amend an earlier row without having 2 entries in master?

 

[XrayLemi]

Hello again Yongle!
I will look at all you gave me. It will take me a while to figure out your clues. Before you even posted the code you posted, I came up with this.....

I made a module with this code

Sub OpenWorkbook()
Workbooks.Open "S:\Radiology\FLUORO LOG BOOKS\Test List.xlsm"
End Sub

In the sheet I created this code

If Target.Cells.Count > 6 Then Exit Sub
  If Not Intersect(Target, Range("H6:H5000")) Is Nothing Then
    With Target(1, 6)
     Call OpenWorkbook

This actually works, BUT it probably can be done alot easier your way. I will try things out and see.
Thank you very much for the response,
Jim

 

[XrayLemi]

Hi Yongle.
Got your way to work. Allot less "messy".
As for amending an earlier row.... I don't have to worry about that. once a row is completed, It is locked and cannot be edited. The only way a change can be made after that is through administration.
Thanks again for the Help!
Jim