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:
- Open the master workbook and press Alt + F11 to open the Visual Basic Editor.
- In the Project Explorer pane, right-click on the VBAProject for the master workbook and select Insert Module.
- 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
- 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.
- Replace "A1:B10" with the range of cells that contains the data you want to import.
- 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.