Hi,

Can anyone guide me to understand, which is the best way of linking workbooks.

I have a master budget which pulls data from several other workbooks.

However, there are several drawbacks for such linkages. If the destination file is opened before the source file or if someone else is accessing the source file which i do then in such situations the destination file give ref# error.

In short, I want to know which is the best way of linking workbooks together.
Also, if macros could be a option.

Thanks,
Balesh

Hi Balesh,

Linking workbooks can be a great way to consolidate data from multiple sources into one master workbook. However, as you mentioned, there can be some drawbacks to this approach.

One alternative to linking workbooks is to use a macro to import the data from the source workbooks into the master workbook. This can be a more reliable approach, as it doesn't rely on external links that can break if the source workbook is moved or renamed.

Here are the steps to create a macro that imports data from a source workbook:

1. Open the master workbook and press Alt + F11 to open the Visual Basic Editor.
2. In the Project Explorer pane, right-click on the VBAProject for the master workbook and select Insert Module.
3. In the new module, enter the following code:
   
   
   Formula:

   Sub ImportData()
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    
    ' Open the source workbook
    Set wbSource = Workbooks.Open("C:\Path\To\SourceWorkbook.xlsx")
    
    ' Set the source worksheet and destination worksheet
    Set wsSource = wbSource.Worksheets("Sheet1")
    Set wsDest = ThisWorkbook.Worksheets("Sheet1")
    
    ' Copy the data from the source worksheet to the destination worksheet
    wsSource.Range("A1:B10").Copy wsDest.Range("A1")
    
    ' Close the source workbook
    wbSource.Close SaveChanges:=False
End Sub 

4. Replace "C:\Path\To\SourceWorkbook.xlsx" with the path to your source workbook, and "Sheet1" with the name of the worksheet that contains the data you want to import.
5. Replace "A1:B10" with the range of cells that contains the data you want to import.
6. Save the macro and close the Visual Basic Editor.

To run the macro, simply press Alt + F8 to open the Macro dialog, select the ImportData macro, and click Run.

This approach can be more reliable than linking workbooks, as it doesn't rely on external links that can break. However, it does require you to manually run the macro each time you want to import the data.

This can help you. i guess please follow the step, site also have how to combine multiple worksheet or workbook

Combine or Merge Excel Files into one Excel spreadsheet using Macro