Macro to auto update another excel file


Hi, I am trying to write a macro that opens up another excel document titled "Obligations" and then copies several sections of the document and then paste the values to an excel document titled, "MER" on active sheet "IS" in an area on the this workbook.   




The code below will read data from a workbook and transfer the values found in a specified range of cells to a similarly specified range in the workbook that contains the code. Paste the code in a standard code module (by default 'Module1'). Set the values of the four constants at the top of the code to those that you require in your own environment.

Option Explicit
Sub ReadData()
    ' 21 Dec 2017
    Const SourceSheet As String = "Credit Note"
    Const SourceRange As String = "A1:H14"
    Const TargetSheet As String = "IS"
    Const TargetRange As String = "A1"
    Dim WbS As Workbook                         ' Source workbook
    Dim Ffn As Variant                                ' full file name (with path)
    Dim Arr As Variant
    Dim Rng As Range
    Ffn = Application.GetOpenFilename("Excel-files,*.xls*", _
                                      1, "Select the source workbook", , False)
    If VarType(Ffn) = vbBoolean Then Exit Sub   ' user cancelled
    Application.ScreenUpdating = False
    Workbooks.Open Ffn, ReadOnly:=True
    Set WbS = ActiveWorkbook                    ' set Source workbook
    On Error Resume Next
    Arr = WbS.Worksheets(SourceSheet).Range(SourceRange).Value
    If Err Then
        MsgBox "An error occurred reading data from the source." & vbCr & _
               "The likely reason is that the specified worksheet" & vbCr & _
               "doesn't exist in that workbook.", _
                vbCritical, "Data could not be retrieved"
    End If
    On Error GoTo 0
    ' paste data to this workbook
    Set Rng = ThisWorkbook.Worksheets(TargetSheet).Range(TargetRange)
    Rng.Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
    WbS.Close xlDoNotSaveChanges
    Application.ScreenUpdating = True
End Sub
The code uses Window's own file picker to let you pick the source workbook. The destination workbook is the one containing the code. You can easily change that yourself.

Answer the Question

You must create an account to use the forum. Create an Account or Login