Excel VBA Import From Another Workbook: A Safe Guide

VBA & macros · Updated November 21, 2026

To import a worksheet with VBA, open the source workbook, copy the required worksheet into the workbook containing the macro, and close the source without saving. The example below does that in Excel for Microsoft 365 on Windows; if you need only selected cells, values, or a repeatable data refresh, the right approach may be different.

What “import from a workbook” can mean

Excel does not have one VBA command called “import a workbook.” Usually, the task is to bring information from one workbook into another. You can copy a complete worksheet, copy a range of cells, transfer only values, or read data into an existing sheet. These choices matter: copying a sheet can preserve formulas and formatting, while copying values avoids bringing formulas and external links along.

There is a second possible meaning: importing VBA code from another workbook. That is a separate operation involving a code module, not the workbook’s cell data. This guide focuses on importing worksheets and data, then explains the code-module distinction in the FAQs.

How the worksheet-copy method works

The macro opens a source file as read-only, identifies a worksheet by its tab name, and uses the worksheet’s Copy method to place a complete copy in the workbook containing the macro. It then closes the source file without saving. The copied sheet can include cell contents, formulas, formatting, and worksheet-level features; it is not the same as copying just a block of values.

In VBA, ThisWorkbook means the workbook where the macro code is stored. ActiveWorkbook means whichever workbook is currently active, which can change when another file opens. Using ThisWorkbook as the destination helps prevent the imported sheet from ending up in the source file by mistake.

Copy a complete worksheet with VBA

Save a copy of the destination workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. Replace the sample path and sheet name with the actual source file path and worksheet tab name. The destination workbook must contain at least one worksheet.

Sub ImportWorksheetFromWorkbook()
    Const SOURCE_PATH As String = "C:\Data\Source.xlsx"
    Const SOURCE_SHEET As String = "Data"

    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim importedSheet As Worksheet

    On Error GoTo CleanFail

    ' Open the source without making changes to it.
    Set sourceBook = Workbooks.Open( _
        Filename:=SOURCE_PATH, UpdateLinks:=0, ReadOnly:=True)
    Set sourceSheet = sourceBook.Worksheets(SOURCE_SHEET)

    ' Copy the complete sheet to the end of the macro's workbook.
    sourceSheet.Copy After:=ThisWorkbook.Worksheets( _
        ThisWorkbook.Worksheets.Count)
    Set importedSheet = ThisWorkbook.Worksheets( _
        ThisWorkbook.Worksheets.Count)

    sourceBook.Close SaveChanges:=False
    Set sourceBook = Nothing

    MsgBox "Imported worksheet: " & importedSheet.Name
    Exit Sub

CleanFail:
    If Not sourceBook Is Nothing Then
        sourceBook.Close SaveChanges:=False
    End If
    MsgBox "Import failed: " & Err.Description, vbExclamation
End Sub

To add the code, open the destination workbook and choose Developer > Visual Basic. In the Visual Basic Editor, choose Insert > Module and paste the procedure into the module. If the Developer tab is hidden on Windows, enable it through File > Options > Customize Ribbon. Save the destination as an Excel Macro-Enabled Workbook (.xlsm), then run the macro from the editor or use Alt+F8, select the procedure, and choose Run.

The code identifies the sheet by its tab name, not its position. A missing file, misspelled sheet name, or inaccessible path triggers the error handler. Excel may adjust the copied worksheet’s name if the destination already has a sheet with that name; inspect the new tab and rename it if your workflow requires a particular name.

Import only a range or values

If the source sheet is large but you need just a table, copying the whole worksheet can bring unnecessary formatting, formulas, hidden rows, or other content. A range transfer is more focused. One common approach is to open the source workbook, set references to the source and destination ranges, and assign the source range’s Value to the destination range’s Value. That transfers the displayed results as values, rather than recreating source formulas.

Before using a range-based macro, decide how to handle varying row counts. A fixed address such as A1:F500 is straightforward when the layout is guaranteed. If the source data grows, determine the last occupied row or use an Excel Table with a stable name. Also decide whether the import should replace existing destination data, append new records, or stop when it finds a duplicate. Those are different business rules, so a macro should not silently guess.

  • Copy a complete sheet when the destination needs the original layout, formulas, and sheet-level structure.
  • Copy a range with formulas when calculations should be carried into the destination and their references are appropriate there.
  • Copy values when the destination should contain a snapshot of results without dependence on source formulas.
  • Append rows when each import is a new batch; define a duplicate-checking rule before automating repeated runs.

Set the source path and workbook behavior

A full file path is the simplest way to make a macro predictable, but it will fail if the file is moved or the user has a different folder structure. For a shared workflow, consider asking the user to choose the source file or storing the path in a clearly labeled cell or configuration sheet. Validate the selected file and the expected worksheet before changing destination data.

The sample uses ReadOnly:=True so the source is not opened for editing, and UpdateLinks:=0 to avoid automatically updating external links during the open operation. This does not make imported formulas independent: a copied formula may still refer to another workbook. If you require a self-contained snapshot, importing values is often more appropriate. If you require live linked calculations, inspect the formulas and link behavior deliberately.

If the source workbook is already open, Excel may reuse that open workbook rather than create a second independent copy. In a more advanced routine, check the open workbooks first and decide whether to use the existing instance or stop with a clear message. Avoid relying on whichever workbook happens to be active.

Common problems and practical checks

  • “Subscript out of range”: The named worksheet may not exist, or its tab name may differ by a space or punctuation. Check the exact tab name, including leading or trailing spaces.
  • File not found: Confirm the spelling, extension, folder, and access permissions. A network or cloud location may be unavailable when the macro runs.
  • Unexpected formulas or links: A copied sheet retains formulas, and their references can behave differently in the destination. Review formulas or import values instead.
  • Duplicate or changed sheet name: A destination workbook cannot contain two worksheets with the same tab name. Confirm the imported tab’s final name before later code refers to it.
  • Macro will not run: Confirm that the file is saved as .xlsm and that its macros are permitted by the organization’s security settings. Do not enable macros in an untrusted file just to make an import work.
  • Wrong workbook changed: Qualify workbook and worksheet references explicitly. Prefer ThisWorkbook for the macro’s destination rather than depending on the active window.

For a regular import process, also consider what happens if the macro stops halfway through. Test with copies of both workbooks, verify the imported row or sheet count, and make the macro report completion only after the required steps finish.

VBA compared with Power Query and manual copying

VBA is useful when the import is part of a larger sequence—for example, selecting a file, copying a sheet, renaming it, and then running other workbook-specific actions. It offers flexibility, but it requires macro permissions and careful maintenance when file paths or layouts change.

Power Query is often a better fit for recurring imports of tabular data that need cleaning, filtering, or combining. In Excel for Microsoft 365 on Windows, start from Data > Get Data and choose a suitable source, such as From File > From Workbook. Load the result to a worksheet or the data model, then use Refresh when the source changes. Power Query does not simply duplicate every worksheet feature; it is designed to shape and load data.

Manual copying is reasonable for a one-time transfer or a small file when the person doing the work can check the result. It avoids writing a macro, but repeated manual steps are easier to perform inconsistently. Choose based on whether the goal is a one-time copy, a refreshable data connection, or a controlled multi-step automation.

Security, compatibility, and saving

Run macros only from workbooks and code you trust. A macro can alter or delete workbook content, and Excel’s macro security settings may block code downloaded from the internet or received by email. Keep an untouched backup of important source data and test the import on sample files before using it in a production process.

Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the desktop Excel application to run this procedure. Excel for Mac supports VBA, but file paths differ from Windows paths, and keyboard shortcuts can depend on the keyboard and macOS settings. On Windows, Alt+F11 opens the Visual Basic Editor and Alt+F8 opens the Macro dialog; on Mac, Option+F11 and Option+F8 may require the Fn key on some keyboards. Use the Developer tab or Excel’s macro controls if a shortcut is intercepted.

Frequently Asked Questions

Can VBA import data without opening the source workbook visibly?
VBA can open a workbook with its window hidden, but the workbook is still opened by Excel. The sample keeps the method simple and opens it normally as read-only. If hiding it is important, add and test that behavior carefully, and ensure the source is always closed even when an error occurs.

Does copying a worksheet copy its formulas and formatting?
Yes. Copying a complete worksheet generally carries its cell contents and formatting, including formulas, and may carry other sheet-level features. Formula references may still point to the source workbook or otherwise change behavior, so inspect them if the destination must be self-contained.

How do I import a VBA module from another workbook?
That is different from importing sheet data. In the Visual Basic Editor, a module can be exported from one VBA project and imported into another, subject to project access and security settings. The destination must be a macro-enabled workbook, and code should be reviewed before it is run.

Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA. Open the macro-enabled file in the Excel desktop application to execute the procedure.

How can I avoid importing the same worksheet repeatedly?
Check whether the destination already contains the intended sheet or import identifier before copying. Then choose an explicit rule: replace the existing sheet, stop and notify the user, or create a uniquely named copy. A safe rule depends on whether the import is meant to refresh a snapshot or preserve historical versions.

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.