Excel VBA: Import Data From Another Workbook Step by Step

VBA & macros · Updated November 21, 2026

To import data from another workbook with Excel VBA, open the source file, identify its worksheet and range, then assign that range to a sheet in the workbook containing the macro. The example below prompts for a file and copies the source sheet’s used range as values; save a copy of your workbook before running it, because macro changes cannot be undone with Ctrl+Z.

What VBA import means

A VBA import is a procedure that moves information from one Excel workbook into another. The macro can open a source workbook, read a particular worksheet or range, and place its contents in a destination workbook. That is different from linking to another file with a formula: an import usually creates a local copy of the data, while a link continues to depend on the source file.

This approach is useful when the same task happens repeatedly, when a workbook needs to combine files from a folder, or when users need a button-driven process rather than a sequence of manual steps. The exact macro depends on what “import” means for the task. You might want values only, formulas, formatting, a whole worksheet, or a filtered subset. The sample in this article imports cell values from one named source sheet.

The instructions target desktop Excel for Microsoft 365 on Windows. Excel for the web cannot run VBA macros. Excel for Mac supports VBA, but keyboard shortcuts and some file-access details can differ; check the Mac notes below before adapting a Windows workflow.

Choose the right import method

VBA is not the only way to bring in data. Consider how often the source changes and whether the task needs automation before choosing a method:

  • Copy and paste: Suitable for a one-time transfer or a small, occasional task. It is straightforward, but repeated manual copying is easy to get wrong.
  • Workbook links or formulas: Useful when destination cells should reflect values in a source workbook. Links can become inconvenient if the source file moves, is renamed, or is unavailable.
  • Power Query: Often a better fit for repeatable data cleaning and combining files. In Microsoft 365 for Windows, find it on the Data tab under Get & Transform Data. It can refresh a query without requiring a custom VBA macro.
  • VBA: Useful when the process needs custom actions, prompts, buttons, or coordination with other workbook operations. A macro also needs appropriate permissions and maintenance.

For example, a monthly report that imports a fixed sheet from a selected workbook may suit VBA. A recurring process that combines dozens of similarly structured files and removes unwanted rows may be easier to maintain in Power Query. Neither method is automatically more accurate: the source layout and validation rules matter.

Prepare the workbooks

Before writing or running the macro, decide which workbook contains the code, where imported data will go, and which source cells count as data. The example expects a source sheet named Data and a destination sheet named ImportedData. Create the destination sheet in the macro workbook first, or change the sheet name in the code to match an existing tab exactly.

  • Save the workbook that will hold the macro as an Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file cannot store VBA code.
  • Keep the source workbook closed when you run the sample. The macro opens it read-only, so it does not need to save changes to that file.
  • Check that the source sheet name is stable and that its data is arranged in a continuous block, with headers in the first row if appropriate.
  • Use a dedicated destination sheet. The sample clears existing cell contents on that sheet before writing the new import.

These checks prevent common mismatches. A tab called Data 2026 is not the same name as Data, and a file containing several sheets does not tell the macro which one you intend to import. If the source has a known address such as A1:F500, using that explicit range can be safer than treating every used cell as part of the dataset.

VBA macro to import values

In desktop Excel on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste this procedure into the module. On many Mac keyboards, Option+F11 opens the editor; the exact shortcut can depend on the keyboard, and the editor is also available from Excel’s Tools > Macro menu. Save a copy before running the code.

Option Explicit

Public Sub ImportDataFromWorkbook()
    Dim filePath As Variant
    Dim sourceWb As Workbook
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    Dim sourceRange As Range
    Dim oldScreenUpdating As Boolean

    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo CleanFail

    ' Ask the user to choose the source workbook.
    filePath = Application.GetOpenFilename( _
        FileFilter:="Excel Workbooks (*.xlsx;*.xlsm;*.xls),*.xlsx;*.xlsm;*.xls", _
        Title:="Select the workbook to import")
    If filePath = False Then Exit Sub

    Set targetWs = ThisWorkbook.Worksheets("ImportedData")
    Application.ScreenUpdating = False

    ' Open the source without editing it or updating external links.
    Set sourceWb = Workbooks.Open( _
        Filename:=CStr(filePath), ReadOnly:=True, UpdateLinks:=0)
    Set sourceWs = sourceWb.Worksheets("Data")
    Set sourceRange = sourceWs.UsedRange

    ' Replace destination contents with the source values.
    targetWs.Cells.ClearContents
    targetWs.Range("A1").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value

    sourceWb.Close SaveChanges:=False
    Set sourceWb = Nothing
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "Import complete.", vbInformation
    Exit Sub

CleanFail:
    If Not sourceWb Is Nothing Then sourceWb.Close SaveChanges:=False
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "Import stopped: " & Err.Description, vbExclamation
End Sub

To run it, return to Excel, press Alt+F8 on Windows, select ImportDataFromWorkbook, and choose Run. On Mac, use Tools > Macro > Macros if the Windows shortcut does not apply. Choose the source file when prompted. The destination receives the imported values beginning at A1.

How the macro works

Application.GetOpenFilename displays a file picker and returns the chosen path. If the user cancels, the procedure exits without opening a workbook. ThisWorkbook means the workbook containing this VBA code, not whichever workbook happens to be active. That distinction helps ensure the destination is consistent even when other workbooks are open.

The code opens the selected file with ReadOnly:=True, which avoids editing the source, and uses UpdateLinks:=0 to avoid updating external links as the workbook opens. It then identifies the worksheet named Data and reads its UsedRange. The range is assigned directly to a resized destination range, rather than copied through the clipboard. This transfers cell values efficiently without bringing over source formatting.

In this macro, the source’s formulas contribute their calculated results when the range is assigned through .Value; the formulas themselves are not recreated in the destination. Formatting, comments, data validation, and column widths are not copied either. If those elements are required, use an appropriate copy operation or add separate code to reproduce them, and test the result on a copy.

The error handler attempts to close the source workbook without saving and restores the previous screen-updating setting. It also displays an error message if a sheet is missing or another problem interrupts the procedure. The code does not create missing sheets automatically: that is intentional, because silently creating a sheet with an unexpected layout can hide a setup mistake.

Adjust the range and import behavior

UsedRange means the worksheet area Excel considers used; it is not always identical to the visible data table. Formatting applied far below or to the right of the actual records can enlarge the reported range. If that happens, the import may include empty rows or columns. Clean up unnecessary formatting in the source, or replace the UsedRange line with a known range that matches the actual layout.

For a fixed dataset in A1:F500, for instance, the source range could be set explicitly in VBA using Set sourceRange = sourceWs.Range("A1:F500"). For a table that grows over time, use a structured Excel Table and adapt the macro to reference that table’s data body range. A fixed address is predictable but must be updated when the dataset grows; a dynamic range is more flexible but needs a reliable rule for locating the data.

The line targetWs.Cells.ClearContents removes values and formulas from every cell on the destination sheet, but it leaves formatting in place. Do not use it on a sheet that contains unrelated calculations or notes. If the imported area is known, clear only that area instead, or import to a fresh staging sheet and review it before replacing the existing report. Also note that clearing the destination and then encountering a problem can leave it empty, so retaining a backup or using a staging workflow is prudent.

To preserve formulas rather than their displayed results, the assignment would need to use the source formula property and a destination range of matching dimensions. To copy formatting as well, a range copy operation can be used, but clipboard-based copy behavior needs more care and can carry unwanted source features. Choose deliberately: a values-only import is often preferable for a clean data handoff, while a faithful worksheet copy is a different task.

Common problems and troubleshooting

  • “Subscript out of range”: Check the exact worksheet tab names in both workbooks. The names in the procedure must match, including spaces.
  • The import contains too many blank rows: The source sheet’s used area may have been expanded by old formatting. Use a known range or a well-maintained Excel Table.
  • The macro does not appear in the macro list: Confirm the code is in a standard module, the file is saved as .xlsm, and macros are permitted by your organization’s security settings.
  • Values differ from the source: The import copies calculated values, not formulas. Check whether source formulas have recalculated and whether the intended result is a value, formula, or formatted copy.
  • The destination was cleared: The procedure clears the destination before writing. Use a dedicated sheet or change the clearing logic so it only affects the intended import area.

When troubleshooting, test with a small copy of the source workbook and inspect the destination after each run. If source files have inconsistent layouts, add checks for expected headers and column counts before clearing or replacing destination data. A macro that verifies the structure can stop with a useful message rather than quietly importing shifted or unrelated columns.

Security, file format, and Mac considerations

Only run VBA from workbooks and sources you trust. A macro can change or delete workbook content, and enabling macros from an unknown file is a security risk. On Windows, macro controls are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Follow your organization’s policy; do not enable all macros simply to make one file run. Excel may show a security warning or block a file downloaded from the internet.

Use .xlsm for a workbook that must retain VBA. Keep a separate backup before running a macro that clears or replaces data, because Excel’s Undo history does not reverse VBA actions. If the macro is used by others, document the expected sheet names, source layout, and destination behavior so that users can tell what the procedure will change.

Excel for Mac can run many VBA procedures, but this example is written for desktop Excel and Windows file workflows. Use the Mac Visual Basic Editor and macro menu if Windows shortcuts are unavailable, and test file selection with the actual folders and file types used on the Mac. Excel for the web can open an .xlsm workbook but cannot execute its VBA; use desktop Excel or consider Power Query or another supported automation method.

Frequently Asked Questions

Can VBA import data without opening the source workbook?
Not with the simple method shown here: it opens the source in Excel, read-only, to access its worksheet cells. Other approaches can read workbook files through external tools or connections, but they involve different setup and limitations.

Does this macro import formulas and formatting?
No. The sample assigns cell values, so it brings calculated results into the destination without the original formulas or formatting. Use a different approach if the destination needs formulas, styles, widths, or other worksheet features.

Can I import from a closed workbook?
The source is closed before the macro starts, but VBA opens it during the import and closes it afterward. It is therefore closed from the user’s starting point, not accessed without being opened by Excel.

Why does the macro say a worksheet is missing?
The source workbook may not contain a sheet named exactly Data, or the destination workbook may not contain ImportedData. Change the corresponding names in the code or rename the tabs, then run the macro again.

Can I run VBA in Excel for the web?
No. VBA macros run in the desktop Excel application, not in Excel for the web. If browser-based refresh or collaboration is essential, investigate Power Query availability or Office Scripts for the task and environment.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.