Loop to copy files and deposit into another location

[dturgel]

In an answer to a previous post, AlphaFrog gave me the following code that allowed me to loop through subfolders called 'Pricing' and change data in a cell in the one file in that folder.

What I'm trying to do now is instead of manipulating the contents of the excel file, I'm trying to copy that file and deposit it into another specified location on the same drive (I'd like to do this for all of the files so that I can get them all in the same folder). After I post the original code below I will show you what I've tried that has not work - any suggestions would be greatly appreciated (I'm using Excel 2010).

Sub Accounts_PricingLoopNorthDakota()
    Dim FSO As Object, fsoFile As Object, fsoSubfolder As Object
    Dim strPath As String
    
    MsgBox "Please choose the Main Accounts folder."
    Application.DisplayAlerts = False
    ChDrive "G"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "G:\"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        strPath = .SelectedItems(1) & "\"
    End With
    
    Application.ScreenUpdating = False
    Set FSO = CreateObject("Scripting.FileSystemObject")
    'Loop through each subfolder in main path
    For Each fsoSubfolder In FSO.GetFolder(strPath).Subfolders
        'Test if "Pricing" subfolder exists
        If FSO.FolderExists(fsoSubfolder.Path & "\Pricing\") Then
            'Loop through each Excel file in subfolder\Pricing\
            For Each fsoFile In FSO.GetFolder(fsoSubfolder.Path & "\Pricing\").Files
                
                'Open workbook
                With Workbooks.Open(Filename:=fsoFile.Path)
                    'Change First Worksheet Mkt Curve Cell Q63 to 320M (North Dakota Premium)
                    .Worksheets(1).range("Q63") = 320000000
                    'Save and Close Workbook
                    .Close SaveChanges:=True
                End With
                    
            Next fsoFile
        End If
    Next fsoSubfolder
    Application.ScreenUpdating = True
    Set FSO = Nothing
    
End Sub

What I tried to do was cut out the operation in the middle and replace it with the following code:

For Each fsoFile In FSO.GetFolder(fsoSubfolder.Path & "\Pricing\").Files
                FileCopy
                FilePaste Dir(G:\CLASH\Reports\UW Meeting Exhibits\2016 UW Meeting Exhibits\1Q\ERC Calculations)
            Next fsoFile

The FilePaste line is no good (I'm not even sure if the FileCopy will work).
I also tried to use 'In'
I first wrote

FilePaste In Dir(G:\CLASH\Reports\UW Meeting Exhibits\2016 UW Meeting Exhibits\1Q\ERC Calculations)

but that went nowhere

Any suggestions?

Daniel

 

[John_w]

fsoFile is a FileSystemObject File object, so you want its Copy method - https://msdn.microsoft.com/en-us/library/6973t06a(v=vs.84).aspx

For Each fsoFile In FSO.GetFolder(fsoSubfolder.Path & "\Pricing\").Files
    fsoFile.Copy "G:\CLASH\Reports\UW Meeting Exhibits\2016 UW Meeting Exhibits\1Q\ERC Calculations\"
Next fsoFile

Note the trailing backslash to correctly specify the destination folder. Also note that the Overwrite argument is not specified which means that existing files with the same name in that folder are overwritten.

 

[dturgel]

Ok fantastic, thanks so much John!
Per your comment at the end, how do I specify the Overwrite argument if I want to avoid overwriting files with the same name?

Daniel