Copy multiple files from multiple folders into a specified folder?

Question

I need to copy 20+ files, each housed by unique folders, into a single folder.

I created the below code.

I get

Compile Error: Object required.

Folder name is the date of the report (eg. 090118), hence, I decided to use a loop until the end of the month (931). I also added error handling code to skip the holidays and weekends.

Sub CopyFiles()

    Dim NewFolder As String
    Dim NDay As Long
    Dim FileName As String
    Dim Month As Variant

    Set Month = InputBox("Enter month, eg. 01-January")
    NewFolder = "C:\Results\Trading\2018\" & Month & "\Backtest Daily Files\Daily GS\" 'Don't forget to edit this
    NDay = 901

    On Error Resume Next

    Do While NDay < 931

        FileName = Dir("C:\Reports\2018\" & Month & "\0" & NDay & "18\GS_Futures*.cs*")

        FileCopy FileName, NewFolder

        NDay = NDay + 1

    Loop

End Sub

Each folder contains several files but I only need to copy one specific file. There's a common factor when it comes to the file name, if this helps. For example: C:\Folder_01\Balance_Sheet_01 C:\Folder_02\Balance_Sheet_02 . . . C:\Folder_20\Balance_Sheet_20 — Jandhi
– Jandhi, Commented Oct 8, 2018 at 6:23

SierraOscar · Accepted Answer · 2018-10-08 08:29:50Z

1

This might not be the most efficient way, however you could use a loop to select the file and then move it useing copyfile.

Sub Move_File()
Dim myFSO As Object
Dim startFolder As String, endFolder As String
Dim startFile As String
startFile = "test.xls"

For i = 1 To 20
startFolder = Range(Cells(i,2),Cells(i,2))
endFolder = "C:\Test\"
myFSO.copyfile startFolder & startFile, endFolder & endFile, True
Next i

End Sub

edited Oct 8, 2018 at 8:29

SierraOscar

17.7k6 gold badges43 silver badges71 bronze badges

answered Oct 8, 2018 at 7:05

Turtle

1113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SierraOscar Over a year ago

Just an FYI, startFolder = Range(Cells(i,2),Cells(i,2)) can be replaced with startFolder = Cells(i, 2).value

Jandhi Over a year ago

Thank you, @Sam! Could you kindly comment on my edited question? I tried using Turtle's answer but it didn't quite fit with my requirements at the moment. I created a code but unfortunately, it won't run.

Turtle Over a year ago

Why are you using 'Set Month'? Deleting the 'Set' and just leaving: 'Month = InputBox("Enter month, eg. 01-January")' I don't get any errors anymore.

Collectives™ on Stack Overflow

Copy multiple files from multiple folders into a specified folder?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related