VBA & Macro Problems

Copy Files from Multiple Folders with an Excel VBA Macro

Kushani NimanthikaKushani Nimanthika Nov 20, 2026 869 views

Question details

Copy files from full paths listed in an Excel worksheet into one destination folder, retaining their file names.

Copy Files from Multiple Folders with an Excel VBA Macro
Product
Microsoft Excel
Device & OS
not provided
Scenario
The worksheet contains source file paths in column A and a destination folder path in cell B2.
Observed behavior
The desired result is a single destination folder containing copies of the listed files.
Before you start

Create the destination folder and check that each source path includes the file name and extension. The macro below assumes Windows-style paths and stops at the first blank cell in column A.

Solution 1Recommended

Run a VBA macro to copy the listed files

Use this macro in desktop Excel to copy files from contiguous paths in column A to the folder in B2.

1
Enter the paths

In the worksheet, enter one complete source file path per cell starting at A2. Enter the existing destination folder path in B2.

2
Open the VBA editor

Press Alt+F11, then select Insert > Module in the VBA editor.

3
Paste the macro

Paste this code into the module: Sub CopyFiles() Dim ws As Worksheet Dim srcPath As String, dstFolder As String, dstFile As String Dim r As Long, copied As Long, skipped As Long On Error GoTo CopyError Set ws = ActiveSheet dstFolder = Trim(CStr(ws.Range("B2").Value)) If dstFolder = "" Then MsgBox "Enter a destination folder in B2." Exit Sub End If If Right(dstFolder, 1) <> "\" Then dstFolder = dstFolder & "\" r = 2 Do While Trim(CStr(ws.Cells(r, 1).Value)) <> "" srcPath = Trim(CStr(ws.Cells(r, 1).Value)) dstFile = dstFolder & Mid(srcPath, InStrRev(srcPath, "\") + 1) If Dir(dstFile, vbNormal Or vbReadOnly Or vbHidden Or vbSystem Or vbArchive) = "" Then FileCopy srcPath, dstFile copied = copied + 1 Else skipped = skipped + 1 End If r = r + 1 Loop MsgBox copied & " file(s) copied; " & skipped & " existing file(s) skipped." Exit Sub CopyError: MsgBox "Copy stopped at row " & r & ": " & Err.Description End Sub

4
Save and run

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Return to the worksheet containing the paths, press Alt+F8, select CopyFiles, and click Run.

5
Check the results

Open the destination folder in File Explorer and check the copied files. If an error message identifies a row, correct its path or permissions before running the macro again.

Run a VBA macro to copy the listed files
Duplicate file names: The macro skips files whose names already exist in the destination. If two source paths have the same file name, the first copied file is retained and the later one is skipped.
Free Microsoft Office alternative

Manage your file lists in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Excel workbooks. Move your file-path lists to WPS with ease, but check macro compatibility before relying on an Excel VBA workflow.

Organize source and destination paths in a spreadsheetOpen and edit Microsoft Excel formats such as XLSX and XLSWork in a familiar spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro copy subfolders or only files?

It copies individual files whose complete paths are listed in column A. It does not scan folders or copy their contents automatically.

What happens if a source file is missing?

The macro stops and displays the worksheet row and error. Correct the path in column A, then run CopyFiles again from Alt+F8; files already in the destination will be skipped.

Why did the macro stop before copying every listed file?

It stops at the first blank cell in column A. Make the source paths contiguous starting at A2, then run the macro again.

Can I use this macro in Excel for the web or on a Mac?

Excel for the web cannot run VBA macros. The code shown uses Windows-style backslashes, so it needs adaptation for macOS paths.