Automating data entry between Excel workbooks saves time and ensures consistency, especially for tasks like pulling monthly reports or consolidating department-level data into a master sheet. Excel offers multiple ways to accomplish this, ranging from simple live links to effective solutions like Power Query or VBA.
In this article, we’ll explore three efficient methods using direct cell linking, Power Query, and VBA Macros. Let’s get started.
Steps to automate data entry from another workbook in Excel:
➤ Open both workbooks and the source file with your dataset and the destination workbook.
➤ In the destination file, select the cell where you want the imported data to appear.
➤ Type an equals sign (=).
➤ Switch to the source workbook and select the desired range.
➤ Press Enter. The destination cell now links to the source data.
➤ Drag the fill handle to link additional cells if needed.
Use Direct Cell Linking for Simple Automation
Direct cell linking is the quickest way to automate data entry from another workbook. It creates a live connection between the source and destination files, ensuring that updates in the source are immediately reflected in your master sheet. This method is perfect for small-scale tasks where you only need a few linked values or simple references.
We’ll use the following workbook called Book2 as our source file:
Steps:
➤ Open both workbooks and the source file with your dataset and the destination workbook.
➤ In the destination file, select the cell where you want the imported data to appear.
➤ Type an equals sign (=).
➤ Switch to the source workbook and select the desired range.
➤ Press Enter. The destination cell now links to the source data.
➤ Drag the fill handle to link additional cells if needed.
Now if we change the value of C2 cell to $12,600, the changes will reflect immediately in our destination workbook.
Use Power Query for Dynamic and Flexible Updates
Power Query is ideal when you need to import entire tables or perform transformations. It allows you to refresh your data dynamically which is perfect for recurring reports or when multiple sheets or tables need to be combined.
Steps:
➤ In the destination workbook, go to the Data tab.
➤ Click Get Data/New Query >> From File >> From Workbook.
➤ Browse for and select your source workbook.
➤ In the Navigator pane, choose the specific sheet or table to import.
➤ Click Load to bring the data into your destination workbook.
➤ To refresh, right-click the imported table and select Refresh.
Now if the value of C2 cell is changed to $12,600, it will update immediately in our destination workbook.
Run VBA Macros for Full Automation
VBA macros let you automate even complex processes, like copying data from multiple files into a master sheet or scheduling updates. With VBA, you can control exactly how and when data is copied, making it a great choice for advanced workflows.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Paste this following macro from Insert tab >> Module:
Sub ImportData()
Dim src As Workbook, dest As Workbook
Dim wsSrc As Worksheet, wsDest As Worksheet
Set dest = ThisWorkbook
Set src = Workbooks.Open("C:\Users\YourPath\Source.xlsx")
Set wsSrc = src.Sheets("Sheet1")
Set wsDest = dest.Sheets("Sheet1")
wsSrc.Range("A1:D11").Copy wsDest.Range("A1")
src.Close False
End SubReplace the code with your original sheet name, data range, output cell and file path of source workbook.
➤ Go to File tab >> Save As >> Choose Excel Macro-Enabled Workbook in the Save as type and hit Save.
➤ Run the macro using the F5 key whenever you need to refresh the data.
Now if the value of C2 cell is changed to $12,600, it will update immediately in our destination workbook.
Frequently Asked Questions
Can I automate data entry without using VBA in Excel?
Yes, you can. Use direct cell linking or Power Query to create live connections between workbooks. These methods update automatically when source data changes and are beginner-friendly compared to VBA scripting.
Does Power Query update automatically when the source workbook changes?
Power Query doesn’t refresh in real-time but can update dynamically. You can refresh manually or set options to refresh when opening the workbook or at specified intervals for automated updates without manual steps.
Is VBA better than Power Query for large-scale data entry tasks?
VBA offers more control and customization for complex tasks like looping through multiple files or conditional copying. Power Query is excellent for transforming and refreshing data but less flexible for advanced, programmatic automation needs.
What happens if I move or rename the source workbook used for linking?
If the source workbook is moved or renamed, direct links or Power Query connections will break. You’ll need to update the file path in Excel’s connection settings or re-establish the link manually.
Can I automate pulling data from multiple workbooks simultaneously?
Yes. Power Query can combine data from several files in a folder, while VBA can loop through multiple workbooks programmatically. Both methods streamline handling multiple sources without repetitive manual copying or pasting.
Wrapping Up
In this tutorial, we explored three effective ways to automate data entry in Excel from another workbook. Use direct cell linking for quick tasks, Power Query for dynamic updates, and VBA macros to unlock advanced automation. Feel free to download the practice file and share your feedback.
