Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to auto update another excel file

0

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.   

Answer
Discuss

Answers

0

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.
Discuss


Answer the Question

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