How can I copy files in same directory to another directory with vba?

Question

I have a file named control.xlsx and want to make macro in this file so control other files in same directory.

I tried like below,

Sub control()

  name = Dir(ActiveWorkbook.path & "\*.xlsx")
  Do While name <> ""
   FileCopy name, ActiveWorkbook.Path & "\new" & "\" & name
  Loop

End Sub

but it doesn't work.. anybody who can help me! I want to modify the copied excel files next. any tips!

Note that *"it doesn't work"* is a completely useless error description. Also make sure that `ActiveWorkbook` is exactly what you want and not `ThisWorkbook` they often get mixed up. • I also recommend to activate `Option Explicit`: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. — Pᴇʜ, Jan 17 '19 at 08:07
Duplicate of https://stackoverflow.com/questions/16943003/vba-to-copy-a-file-from-one-directory-to-another — Sam, Jan 17 '19 at 08:07
Possible duplicate of [VBA to copy a file from one directory to another](https://stackoverflow.com/questions/16943003/vba-to-copy-a-file-from-one-directory-to-another) — Michał Turczyn, Jan 17 '19 at 08:11
@SolarMike Good idea, but this would duplicate all files not only `xlsx`. — Pᴇʜ, Jan 17 '19 at 09:03

score 1 · Answer 1 · answered Jan 17 '19 at 08:57

1

Your code is almost OK.

     Sub control()
        name = Dir(ActiveWorkbook.path & "\*.xlsx")
        Do While name <> ""
           FileCopy name, ActiveWorkbook.Path & "\new" & "\" & name
           name = Dir()
        Loop
     End Sub

answered Jan 17 '19 at 08:57

Rafał B.

487
1
3
19

How can I copy files in same directory to another directory with vba?

1 Answers1