To open an Excel file and copy its data with VBA, use Workbooks.Open to open the source workbook, then assign the source range to a range in the destination workbook. The example below copies cell values into a dedicated worksheet; it reads the source file without saving changes to it.
What the VBA macro does
A macro can automate a routine transfer: open a file at a known path, read a worksheet, place its data in another workbook, and close the source. This is useful for recurring reports, monthly exports, and consolidating data from a consistent template. It also avoids manually switching between open workbooks and selecting ranges.
The example uses ThisWorkbook for the destination. That means the destination is the workbook containing the macro, not necessarily the workbook currently active on screen. It copies the source worksheet’s used range as values. As a result, formulas in the source become their current calculated results; source formatting, column widths, and formulas are not transferred.
Prepare the workbooks and macro
In Excel for Microsoft 365 on Windows, save the destination workbook as an Excel Macro-Enabled Workbook (.xlsm) so it can store VBA. Create a worksheet named Imported in that workbook, and make sure the source file contains a worksheet named Data. Replace the sample path in the code with the actual location and file name.
- Open the destination workbook and press
Alt+F11to open the Visual Basic Editor. - Choose Insert > Module, then paste the macro into the module.
- Save the workbook as
.xlsm. An.xlsxfile cannot store VBA macros. - Before running a macro, save a copy of the workbook. Macro changes cannot be undone with Ctrl+Z.
The source workbook can be an .xlsx file; it does not need to contain macros. The destination needs to be macro-enabled if the code is stored in it. If your macro is stored somewhere else, such as the Personal Macro Workbook, change the destination reference deliberately rather than assuming ThisWorkbook points to the file you want to modify.
Example: open a workbook and copy values
This macro opens the specified file as read-only, copies the used range from its Data sheet to cell A1 of the destination’s Imported sheet, and closes the source without saving. It clears existing cell contents on the destination sheet first, so use a sheet reserved for imported data.
Option Explicit
Sub OpenWorkbookAndCopyValues()
Dim sourcePath As String
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Dim destinationRange As Range
On Error GoTo CleanFail
' Change this to the source file's full path.
sourcePath = "C:\Reports\January.xlsx"
Set destinationSheet = ThisWorkbook.Worksheets("Imported")
Set sourceBook = Workbooks.Open( _
Filename:=sourcePath, ReadOnly:=True, UpdateLinks:=0)
Set sourceSheet = sourceBook.Worksheets("Data")
Set sourceRange = sourceSheet.UsedRange
' Use a dedicated destination sheet: this clears its values and formulas.
destinationSheet.Cells.ClearContents
Set destinationRange = destinationSheet.Range("A1").Resize( _
sourceRange.Rows.Count, sourceRange.Columns.Count)
destinationRange.Value2 = sourceRange.Value2
sourceBook.Close SaveChanges:=False
MsgBox "Data copied to the Imported sheet."
Exit Sub
CleanFail:
' Close the source if an error occurred after it opened.
On Error Resume Next
If Not sourceBook Is Nothing Then sourceBook.Close SaveChanges:=False
On Error GoTo 0
MsgBox "The copy did not finish: " & Err.Description, vbExclamation
End Sub
ReadOnly:=True helps prevent accidental edits to the source. UpdateLinks:=0 tells Excel not to update external links when opening it. The assignment from one equally sized range to another transfers values directly without using the clipboard. The source’s formulas are not copied as formulas, and its number formats are not carried over. The destination sheet’s existing formatting remains, except where it may affect how the pasted values display.
Because the macro clears all contents on Imported, it removes existing values and formulas anywhere on that sheet, not just the previous import area. It leaves cell formatting in place. If that sheet contains notes or other work, remove the destinationSheet.Cells.ClearContents line and clear only a carefully chosen range instead.
Copy values, formulas, or formatting?
“Copy data” can mean different things. The method in the main example is often suitable for importing a report when you need its displayed results but do not want formulas that refer back to the source workbook. It also avoids carrying source styles into a workbook with its own layout.
If you need formulas and formatting as well, replace the range assignment in the example with this copy statement:
sourceRange.Copy Destination:=destinationSheet.Range("A1")
This uses Excel’s copy operation to transfer cell content and formatting, including formulas where present. Formulas that refer to other sheets or workbooks may still depend on those sources or may be adjusted by Excel during the copy. A copied range also does not automatically make the destination sheet an exact duplicate: for example, column widths are worksheet-level settings and may need separate handling. If you want formulas converted to fixed results after copying, paste values as a separate step or use the value-assignment approach from the main example.
Choose the source range carefully
UsedRange is convenient when the data size changes, but it does not always mean “the cells with meaningful data.” Formatting applied far down a sheet can make Excel treat extra cells as used. The transfer may then include blank rows or columns. Inspect the source sheet’s extent if imports unexpectedly become large.
For a fixed layout, set the range explicitly, such as Set sourceRange = sourceSheet.Range("A1:F500"). For a variable number of rows, a macro can find the last populated row in a known column and build a range from that result. The right approach depends on the file: a reliable key column is usually better than guessing from a column that may contain blanks. Also note that the example copies hidden and filtered-out rows if they fall inside the source range; it does not apply a visible-cells-only rule.
Dates and times copied through Value2 are transferred as underlying numeric values. Their appearance depends on the destination’s number format, so a date may display as a number if the destination cells are formatted as General. Apply the appropriate date, currency, or percentage format to the destination when presentation matters.
Run the macro and check the result
In Windows Excel, return to the workbook and press Alt+F8, select OpenWorkbookAndCopyValues, and choose Run. You can also run it from the Visual Basic Editor with the cursor inside the procedure by pressing F5. Check the Imported sheet and confirm that the expected rows, columns, and displayed values arrived before using the results elsewhere.
For repeat use, verify the source path each time the file is moved or renamed. A hard-coded path is straightforward but will fail if the file is no longer at that location. A file picker can be added when the source changes frequently, but the macro should still validate that the selected workbook contains the expected worksheet and data before replacing destination contents.
Common errors and practical fixes
- “Subscript out of range”: Check that the source workbook has a sheet named exactly
Dataand the destination has one namedImported. Sheet names must match, including spaces. - File not found or path error: Check the full path, extension, folder access, and spelling. A mapped network drive may not be available in a different Windows sign-in session.
- Imported range is unexpectedly large: Review the source’s used range for distant formatting or old content. Use a specific range or a more deliberate last-row method.
- Old results remain below the new data: If you remove the full-sheet clearing line, clear the previous import area before writing a shorter replacement. Otherwise, rows from the earlier run can be mistaken for current data.
- Values look wrong: Check the destination number formats, formulas’ calculated results, and whether the source contains errors or blanks. The values-only method does not reproduce source formatting or formulas.
If the source file is password-protected, unavailable, or open in a way that prevents access, the macro may stop at the open step. Do not put a password directly into shared VBA code unless you understand the exposure risk; VBA projects are not a secure place to store secrets. Add error handling appropriate to the actual workflow rather than suppressing every error, since a hidden failure can leave an incomplete import.
Macro security and platform differences
Only enable macros in files from sources you trust. Excel may block macros from files downloaded from the internet or show a security notice; do not bypass that protection for an unknown workbook. For a recurring internal process, follow your organization’s approved macro-signing or trusted-location policy instead of enabling all macros globally.
Excel for Mac supports VBA, but the Visual Basic Editor and keyboard shortcuts can vary with the keyboard and macOS settings. Option+F11 may open the editor; on some keyboards, you may also need the Fn key. The Developer tab or the Macros command is another way to access macros. Use a Mac file path, such as /Users/name/Reports/January.xlsx, in place of the Windows path shown above. Excel for the web can open workbooks but cannot create, edit, or run VBA macros; use the desktop Excel application.
Frequently Asked Questions
Can VBA copy data from a closed Excel file?
Yes. Workbooks.Open opens the file in Excel, so the source does not need to be open beforehand. The example opens it read-only, copies the range, and closes it.
Does the macro copy formulas or just their results?
The main example copies values, so formulas become their current results. Use sourceRange.Copy Destination:=destinationSheet.Range("A1") when you need to copy formulas and formatting too.
Can I copy the data into the workbook I already have open?
Yes. The example writes to ThisWorkbook, the workbook containing the macro. If the code is stored in a different workbook, use an explicit reference to the intended destination rather than relying on the active workbook.
Why does the macro copy extra blank rows or columns?UsedRange can include cells that were formatted or used previously, even if they now appear blank. Inspect the source sheet or define a narrower range based on the actual data layout.
Can I undo a macro with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, and macro changes cannot be reversed with Ctrl+Z. Save a separate copy before running code that replaces or clears data.
Related
- Excel Transpose Shortcut: Flip Rows and Columns Fast
- Transpose Key in Excel: Shortcut and Other Methods
- Tracking Sales in Excel: A Step-by-Step Tutorial
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Function As Boolean: How to Return True or FalseYes. In VBA, declare a function with As Boolean when it should return either True or False, then…
- Excel VBA: How to Check AutoFilter StatusIn Excel VBA, check Worksheet.FilterMode to find out whether a worksheet currently has a filter applied, and check…
- Excel VBA Calendar Date Picker: Options and SetupExcel for Microsoft 365 on Windows does not include a universal VBA calendar date-picker control that you can…
- VBA Quit UserForm: How to Hide or Unload the FormTo quit a VBA UserForm, use Unload Me in a button or event procedure on the form; this…
- Excel VBA Collection vs Dictionary: Which Should You Use?Use a VBA Collection when you need an ordered list of items, optionally addressed by unique string keys;…
- VBA MS Access Cell Color in Excel: Set Cells SafelyAccess does not have Excel-style worksheet cells, so Access VBA cannot color an Access table cell as if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.