Copy Files in Source Path based on Cell Values, then Paste them in Destination Path

[agent_maxine]

Dear Mr. Excel,
After days of searching for answers to no avail, I thought I'd reach out to you. Any help will be greatly appreciated!

I am trying to accomplish the following:

• I have a column (from Cell C54 to C105) that displays the file names (they are results of formulas). Some of them contain null string "".
• My DocRange then is only the cells that contain results other than null string.
• Then it needs to copy all the files in FromPath with same file names as the cell values within DocRange.
• It pastes these files into ToPath.
• It also creates a single, combined Word document that merges/appends all the DOCX files.
• It creates a single, combined PDF document for all the copied files.

I started with Ron de Bruin's codes then tried to add additional features... I am currently getting an error message:

Error Message = "Run-time error 53: File Not Found"
​

Sub Copy_Files()
'This example copy all Excel files from FromPath to ToPath.
'Note: If the files in ToPath already exist it will overwrite existing files in this folder

Dim FromPath As String, ToPath As String, FileExt As String

Dim Company As String
Company = Cells(1, 3).Value

FromPath = Application.ActiveWorkbook.Path & "\" & Company
ToPath = Application.ActiveWorkbook.Path & "\Forms\"

If Right(ToPath, 1) <> "\" Then
    ToPath = ToPath & "\"
End If

Dim DocRange As Range
    Set DocRange = Range("C54:C105").SpecialCells(xlCellTypeVisible)

FileExt = "*.*"

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CreateFolder (ToPath)

For Each c In DocRange
If Not IsNull(cell.Value) Then
    File = Dir(FromPath & FileExt)
    While (File <> "")
        FileCopy FromPath & File, ToPath & File
    File = Dir
    Wend
Else
End If
Next

'FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath

MsgBox "Applicable Forms have been copied into the Folder " & ToPath & "."

End Sub

 

[JLGWhiz]

For Each c In DocRange
If c.Value <> "" Then
    File = c.Value
    While (File <> "")
        FileCopy  FromPath & "\" & File ToPath & File    
    Wend
Else
End If
Next

It looks like you are mixing apples and oranges. You need to either loop the range with the file names or use the Dir function method. But not both. The above would use only the range listing.

 

[agent_maxine]

Thank you for your kind reply. Unfortunately I am still getting the same Error Message ("File not found")...

Sub Copy_Files()
'This example copy all Excel files from FromPath to ToPath.
'Note: If the files in ToPath already exist it will overwrite existing files in this folder

Dim FromPath As String, ToPath As String, FileExt As String

Dim Company As String
Company = Cells(1, 3).Value

FromPath = Application.ActiveWorkbook.Path & "\" & Company
ToPath = Application.ActiveWorkbook.Path & "\Forms\"

If Right(FromPath, 1) <> "\" Then
    FromPath = FromPath & "\"
End If

Dim DocRange As Range
    Set DocRange = Range("C54:C105").SpecialCells(xlCellTypeVisible)

FileExt = "*.*"

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CreateFolder (ToPath)

For Each c In DocRange
If c.Value <> "" Then
    File = c.Value
    While (File <> "")
        FileCopy FromPath & File, ToPath & File
    Wend
Else
End If
Next

'FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath

MsgBox "Applicable Forms have been copied into the Folder " & ToPath & "."

End Sub

 

[JLGWhiz]

FromPath = Application.ActiveWorkbook.Path & "\" & Company

Set DocRange = Range("C54:C105").SpecialCells(xlCellTypeVisible)

These are the two areas that I would suspect of causing the problem. If the FromPath is incorrect it would not find the file. If the DocRange variable Is Nothing then it would not find the file. You can step through the code using the F8 function key and test your variables as the code executes by hovering the mouse pointer over them to display the tool tips. The vb editor must be open when you use the F8 function key.

 

[agent_maxine]

You are correct -- It is the "DocRange" line that results in "Nothing". It still generates Nothing even when I simplify the command as

Set DocRange = Range("C54")

(I should note that it doesn't work when I added "ThisWorkbook.ActiveSheet." before the Range of cells as well.)

The Cell C54 does contain values (not formula) so it shouldn't find "Nothing" in that cell. Any help would be greatly appreciated -- thanks!

 

[JLGWhiz]

You are correct -- It is the "DocRange" line that results in "Nothing". It still generates Nothing even when I simplify the command as

Set DocRange = Range("C54")

(I should note that it doesn't work when I added "ThisWorkbook.ActiveSheet." before the Range of cells as well.)

The Cell C54 does contain values (not formula) so it shouldn't find "Nothing" in that cell. Any help would be greatly appreciated -- thanks!

The best way to test if it is really Nothing is to use a message box

Set DocRange = Range("C54:C105")
MsgBox DocRange.Address

If the address shows in the message box display, then it is not Nothing. If it the message box display is blank then the variable did not initialize.