To open an Excel workbook and copy its data with VBA, use a macro that asks you to select a file, opens it, and transfers the source sheet’s used range to the active sheet. The example below copies cell values into cell A1; it leaves the source file unchanged and does not copy formatting.
A Macro to Open a Workbook and Copy Its Data
In Excel for Microsoft 365 on Windows, place this code in a standard VBA module in the workbook that should receive the data. Before running any macro, save a copy of the destination workbook: VBA changes cannot be undone with Ctrl+Z.
Option Explicit
Sub ImportUsedRangeFromWorkbook()
Dim selectedFile As Variant
Dim destinationWb As Workbook
Dim destinationSheet As Worksheet
Dim sourceWb As Workbook
Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim openWb As Workbook
Dim openedByMacro As Boolean
On Error GoTo HandleError
' Remember the destination before another workbook becomes active.
Set destinationWb = ActiveWorkbook
Set destinationSheet = ActiveSheet
' Ask the user to choose an Excel workbook.
selectedFile = Application.GetOpenFilename( _
FileFilter:="Excel workbooks (*.xls*),*.xls*", _
Title:="Select the source workbook")
' GetOpenFilename returns Boolean False if the user cancels.
If VarType(selectedFile) = vbBoolean Then Exit Sub
' Reuse the source workbook if it is already open.
For Each openWb In Application.Workbooks
If StrComp(openWb.FullName, CStr(selectedFile), vbTextCompare) = 0 Then
Set sourceWb = openWb
Exit For
End If
Next openWb
' Otherwise open it read-only so the import cannot change its file.
If sourceWb Is Nothing Then
Set sourceWb = Workbooks.Open(Filename:=CStr(selectedFile), ReadOnly:=True)
openedByMacro = True
End If
Set sourceSheet = sourceWb.Worksheets(1)
Set sourceRange = sourceSheet.UsedRange
' Write the source values to the destination starting at A1.
destinationSheet.Range("A1").Resize( _
sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value
If openedByMacro Then sourceWb.Close SaveChanges:=False
MsgBox "Data copied to " & destinationSheet.Name & ".", vbInformation
Exit Sub
HandleError:
' Close only a workbook this macro opened; leave previously open files alone.
If openedByMacro And Not sourceWb Is Nothing Then
On Error Resume Next
sourceWb.Close SaveChanges:=False
On Error GoTo 0
End If
MsgBox "The import could not be completed: " & Err.Description, vbExclamation
End Sub
The destination is the workbook and worksheet active when the macro starts, so click the intended destination tab before running it. The macro reads the first worksheet in the selected source workbook. If that workbook was already open, it uses the open copy and leaves it open; otherwise, it opens the file read-only and closes it after copying.
How the Import Works
The file picker returns the selected file path. The macro then checks whether that workbook is already open, which avoids unnecessarily opening and closing a workbook that you may be using. For a file that is not open, Workbooks.Open opens it in read-only mode. The code points to the first worksheet and uses its UsedRange to determine the rectangular area to transfer.
The assignment to the destination range writes the source range’s Value property. This transfers the displayed results stored in cells, including text, numbers, dates, and formula results, rather than copying the formulas themselves. It is a direct range-to-range assignment, not a copy-and-paste operation. That makes it straightforward and generally faster for larger blocks of cells, but it does not bring across formatting, column widths, comments, or other worksheet features.
The destination range is sized to match the source range and begins at A1. If the source’s used range begins somewhere other than A1, its relative layout is preserved: for example, data in source cells C3:D5 is written into destination cells A1:B3. The macro does not delete existing destination content outside the cells it writes.
Install and Run the Macro
- Save a copy of the workbook that will receive the data. If it does not already contain macros, save it as an Excel Macro-Enabled Workbook (
.xlsm) using File > Save As. - Open the destination workbook and select the worksheet where the imported block should begin. The sample starts at A1.
- Open the Visual Basic Editor with Alt+F11 on Windows. In the editor, choose Insert > Module, then paste the code into the new module.
- Return to Excel and run the macro from Developer > Macros: select
ImportUsedRangeFromWorkbookand choose Run. If the Developer tab is not visible, you can also run it from the Visual Basic Editor. - Choose the source workbook in the file dialog. After the macro finishes, review the destination sheet and save the destination workbook if the result is correct.
On a Mac, Excel also supports VBA macros, although the editor shortcut depends on the keyboard; Option+F11 or Fn+Option+F11 may open it. Use Excel’s Visual Basic Editor and its Insert > Module command to add the code. The file picker and Windows-specific Developer-tab instructions may look different on Mac. Excel for the web can open and edit many workbooks, but it cannot create, edit, or run VBA macros; use the desktop Excel app.
Choose What to Copy: Values, Formulas, or Formatting
The sample is intended for a clean data import where you want the source cells’ current results. Because it assigns sourceRange.Value, a source formula such as a total is transferred as its calculated value. This is useful when the destination should contain a snapshot that does not depend on the source workbook’s formulas or links.
To preserve formulas instead, change the assignment’s final property from .Value to .Formula. Formula references can behave differently when moved to a different location or workbook, so check whether they still refer to the intended cells. To copy formatting too, use Excel’s copy-and-paste methods rather than a value assignment; that approach can also transfer other cell features, but it may be slower and requires more care with clipboard state.
- Values: Best for importing a stable table of results without bringing in source formulas.
- Formulas: Appropriate when calculations should remain editable, but inspect references and external links afterward.
- Formatting: Needed if colors, number formats, borders, or styles matter; the example does not copy them.
- A specific range: Use an explicitly named range such as
Range("A1:F200")when you know the exact source boundaries and do not want the whole used area.
Do not assume that UsedRange always means “the current data table.” Excel can consider cells used because they previously held content or received formatting. A sheet with stray formatting far below the data may therefore produce a larger import than expected. For repeatable imports, specifying a known range or defining a named range can be more predictable.
Select a Different Sheet or Destination
The line Set sourceSheet = sourceWb.Worksheets(1) selects the first worksheet in the source workbook’s tab order. To target a worksheet by name, replace it with a reference such as Set sourceSheet = sourceWb.Worksheets("Sales"). The named sheet must exist in every file the macro may open; otherwise, VBA raises an error and the message box reports that the import could not be completed.
The destination is captured from ActiveWorkbook and ActiveSheet before opening the source. That order matters because opening a workbook can make it the active workbook. For a macro stored in a particular destination workbook, using ThisWorkbook instead of ActiveWorkbook can make the destination more explicit. In that case, set the destination worksheet by name as well, for example ThisWorkbook.Worksheets("Import").
To start the destination at another cell, replace Range("A1") with the desired top-left cell, such as Range("B4"). The macro will write the same-sized block from that point. If you import repeatedly and the new dataset may be smaller than the old one, clear the previous output area deliberately before writing; the sample leaves any cells beyond the new block untouched to avoid deleting unrelated data.
Common Problems and Their Fixes
- The macro does not appear or run: Confirm the workbook is saved as
.xlsmand that macros are allowed by your organization’s security policy. Do not enable macros in an unfamiliar file simply to make it work. - The wrong worksheet is imported: The code selects the first worksheet, not necessarily the sheet currently visible when the source file opens. Select the source by its tab name if the correct sheet is known.
- Unexpected blank rows or columns appear: Check the source sheet for old formatting or content far beyond the visible table. Replace
UsedRangewith a specific range or a range determined from the table’s actual last row and column. - Old destination values remain: The macro writes over the matching-size block but does not clear leftover cells. Review the output dimensions and clear only a known import area if appropriate.
- Some cells do not transfer as expected: Merged cells, protected sheets, unusual data layouts, or errors in source formulas can require special handling. A simple rectangular value assignment is best suited to ordinary tabular ranges.
Also check whether the source workbook is already open with unsaved edits. In that case, the macro reads the open workbook’s current in-memory values, which may differ from the last saved version on disk. Save or close the source first if you need the imported data to match the saved file exactly.
Safety and Data-Handling Checks
The macro opens a newly selected source workbook as read-only and closes it without saving changes. It does not save the destination workbook automatically, so you can inspect the result before saving. If the source is already open, the code uses that open workbook and does not close it. These choices reduce the risk of accidentally changing or closing a workbook the user already had open, but they do not replace a backup.
Review the destination before using the imported values in reports or other workbooks. Verify that the selected file and worksheet are correct, that the data begins in the expected cell, and that no important destination cells were overwritten. If the data contains sensitive information, follow your organization’s file access and storage rules; a file picker does not make a selected workbook trustworthy. Macros can make changes that Ctrl+Z cannot reverse, which is why a separate copy is the safest starting point.
When a VBA Macro Is Not Necessary
For a one-time transfer, opening both workbooks and copying the desired range manually may be simpler. If you need to repeat a structured import and refresh it later, Excel’s Power Query tools may be a better fit: they can connect to workbook data and let you refresh the query rather than rerun a custom macro. Power Query is also useful when data needs cleaning or reshaping before it reaches the destination.
Choose VBA when the process needs a button, a file-selection prompt, or custom steps that should run inside Excel. Choose a manual copy for an occasional small task, and consider Power Query for repeatable imports that benefit from a refreshable transformation. The best method depends on whether the priority is a quick transfer, an automated routine, or a maintainable data connection.
Frequently Asked Questions
Can I copy data from a closed Excel workbook?
Yes. The macro opens the selected file read-only, copies the range, and closes that source workbook afterward. You do not have to open the source file yourself first.
Does this macro copy formulas and formatting?
No. It writes the source cells’ values, including formula results, and does not copy formatting. Change the range assignment if you need formulas, or use a copy-and-paste approach if formatting must be preserved.
How do I copy a named sheet instead of the first sheet?
Replace Worksheets(1) with Worksheets("YourSheetName"), using the exact worksheet tab name. The macro will return an error if that sheet is missing from the selected workbook.
Can the macro append data below existing rows?
Not as written: it always begins at A1. Appending requires finding the destination’s next available row and using that cell as the destination. Check for blank rows or other content in the target column so the chosen next row is appropriate.
Will the macro run in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in the desktop version of Excel for Windows or Mac to install and run the macro.
Related
- To-Do List Template in Excel: Create and Use Yours
- To Do List Excel Sheet Template: Make a Useful Tracker
- To-Do List Excel Spreadsheet: Setup, Columns and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Functions: Basics, Examples, and How to Write ThemA VBA function is a named block of code that performs a task and returns a value. In…
- VBA Find: How to Handle a Failed SearchIn Excel VBA, handle a failed Range.Find search by checking whether its result is Nothing before using it.…
- VBA Code to Save a Workbook in Excel: Practical ExamplesTo save the workbook that contains your VBA code, use ThisWorkbook.Save; to save the active workbook, use ActiveWorkbook.Save.…
- Excel VBA Hex Function: Convert Numbers to HexIn Excel VBA, Hex(number) converts a numeric value to a hexadecimal text string: for example, Hex(255) returns FF.…
- Excel VBA AutoFilter Wildcards: Criteria ExplainedYes. In Excel VBA, use * to match any number of characters, ? to match one character, and…
- Excel VBA If Statement Without Then: What Works?No: an Excel VBA If statement must include the keyword Then. Write If condition Then before the action…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.