Select/update/retrieve cells between Excel files

Hi please I need help. I want to use an excel file named for ex:Contracts as my workbook this file contains this cells: model, interval, KmC.
And other excel files as source of data, here is what I want to achieve in

steps:

1- In Contracts when I write model name in B1 the system should choose the file with the same name. nothing will happened just picking the right workbook for the rest.

2- when I write kmc B2 it should be updated in a given cell in the right workbook that has same Model name.

3 - same think as step 2

NB: after updating these values the amount and tax will change in the model file that we are working with.

4 - now I want to retrieve the amount and tax in the workbook that has the same name of the model that we write in the first step.



Please I need guide for what I have to use in each step or what I should learn in the future to do those kind of things: exchange data between file, Data manipulation. Create word documents from excel.

Thanks in advance

You could do something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' this event fires when something changes in the worksheet
    
    ' keep the workbook filename in memory
    Static modelWorkborkFileName As String
    
    Dim sourceDataPath As String
    sourceDataPath = "F:\Temp\"  ' this is the path to find the Excel files to use as source data
    
    Select Case Target.Address
        Case "$B$1" ' this code will run if the cell B1 changed - MODEL
            ' build the complete path to the source data file
            modelWorkborkFileName = sourceDataPath & Target.Value & ".xlsx"
            
        Case "$B$2"
            ' call the procedure that opens and interacts with the source data workbook
            ActiveSheet.Range("C2").Value = OpenSourceDataWB(modelWorkborkFileName, UCase(Target.Value))
            
    End Select
End Sub

Private Function OpenSourceDataWB(workbookFileName As String, whatValueToRetrieve As String) As Variant

    ' open the source data workbook file
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    
    ' check to see if the workbook is where it is expected to be
    If Not fso.FileExists(workbookFileName) Then
        MsgBox "The workbook '" & workbookFileName & "' could not be found", vbCritical, "Missing File"
        Set fso = Nothing
        OpenSourceDataWB = ""
        Exit Function
    End If
    
    Application.ScreenUpdating = False  ' don't display the opened workbook
    
    Dim sourceDataWB As Workbook
    Dim sourceDataWS As Worksheet
    Dim readCellValue As Variant
    
    Set sourceDataWB = Workbooks.Open(workbookFileName)
    Set sourceDataWS = sourceDataWB.Sheets("Sheet1") ' which sheet will be used in the workbook
    
    ' this is an example of reading data from a specific cells in the source workbook
    ' depending on an indicator passed to this function
    Select Case whatValueToRetrieve
        Case "KMC"
            readCellValue = sourceDataWS.Range("B2").Value
        Case "ABC"
            readCellValue = sourceDataWS.Range("C2").Value
    End Select
    
    Set sourceDataWS = Nothing
    sourceDataWB.Close
    
    Set sourceDataWB = Nothing
    Application.ScreenUpdating = True  ' turn displaying on again
    
    OpenSourceDataWB = readCellValue
    
End Function

2- when I write kmc B2 it should be updated in a given cell in the right workbook that has same Model name.

This isn't clear on if the Contracts workbook is updated or the Model workbook is updated

The example will show you one way to react to a cell's change and open a workbook.

Good luck with your project, hopefully, you can use this to figure out the rest.

Thanks man I appreciate your help

You're welcome, good luck with your project.