VBA Code won't go to the bottom

[mraycii]

Let me preface this by saying I am a rank amateur at writing code. I have cobbled this code together using pieces from here and there.

The data set:
Excel file containing ~30,000 rows of data which is file names and directory names.

My mission is as follows:
Copy these files from the current directory path into the destination directory path using the names of the files in the list.

The issue:
The code begins and will and copy 442 files with no issue, however, when the "current directory w/o" p/n transitions to a different directory, "the file is not found" error pops up.

Here is the code:

Option Explicit

Sub CopyFiles()
Dim FSO As Object
Dim PATH, sourcefile As String, dest, DestinationFolderName, SourceFileName, Filename As String
Dim lr, x As Long

Set FSO = CreateObject("Scripting.Filesystemobject")
PATH = Range("F2").Value
lr = Cells(Rows.Count, "B").End(xlUp).Row

If PATH <> "" Then
For x = 2 To lr
SourceFileName = Range("B" & x).Value
DestinationFolderName = Range("H" & x).Value
sourcefile = PATH & "\" & SourceFileName & ".SLDPRT"
'dest = PATH & "\" & DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
dest = DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
If Not FSO.FileExists(sourcefile) Then
MsgBox ("File Not Found in " & sourcefile)
Else
FSO.CopyFile Source:=sourcefile, Destination:=dest
'MsgBox (sourcefile + " Moved to " + dest)
End If
Next x
Else
MsgBox ("Please Insert PATH in cell 'I2'")
Exit Sub
End If
End Sub




Any help which can be offered would be greatly appreciated.

 

[rollis13]

With PATH = Range("F2").Value you are fetching your PATH from a static cell (F2) while you need it to be dynamic (whatever found in the cell in column F referred to the processed row). Change this parte of your macro like this, should do.

'...
    Set FSO = CreateObject("Scripting.Filesystemobject")
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    For x = 2 To lr
        PATH = Range("F" & x).Value               '<- moved down and changed
        If PATH <> "" Then                        '<- moved down
            SourceFileName = Range("B" & x).Value
            DestinationFolderName = Range("H" & x).Value
            sourcefile = PATH & "\" & SourceFileName & ".SLDPRT"
            'dest = PATH & "\" & DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
            dest = DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
            If Not FSO.FileExists(sourcefile) Then
                MsgBox ("File Not Found in " & sourcefile)
            Else
                FSO.CopyFile Source:=sourcefile, Destination:=dest
                'MsgBox (sourcefile + " Moved to " + dest)
            End If
        Else                                      '<- moved up
            MsgBox ("Please Insert PATH in cell 'I2'") '<- moved up
            Exit Sub                              '<- moved up
        End If                                    '<- moved up
    Next x
End Sub

 

[Bosquedeguate]

when the PATH changes you need to define the value of PATH
Move the line
PATH = Range("F2").Value
to inside the loop and change it to: (should be the first line after your For r = ..... statement.
PATH = Range("F" & r).Value

 

[mraycii]

With PATH = Range("F2").Value you are fetching your PATH from a static cell (F2) while you need it to be dynamic (whatever found in the cell in column F referred to the processed row). Change this parte of your macro like this, should do.

'...
    Set FSO = CreateObject("Scripting.Filesystemobject")
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    For x = 2 To lr
        PATH = Range("F" & x).Value               '<- moved down and changed
        If PATH <> "" Then                        '<- moved down
            SourceFileName = Range("B" & x).Value
            DestinationFolderName = Range("H" & x).Value
            sourcefile = PATH & "\" & SourceFileName & ".SLDPRT"
            'dest = PATH & "\" & DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
            dest = DestinationFolderName & "\" & SourceFileName & ".SLDPRT"
            If Not FSO.FileExists(sourcefile) Then
                MsgBox ("File Not Found in " & sourcefile)
            Else
                FSO.CopyFile Source:=sourcefile, Destination:=dest
                'MsgBox (sourcefile + " Moved to " + dest)
            End If
        Else                                      '<- moved up
            MsgBox ("Please Insert PATH in cell 'I2'") '<- moved up
            Exit Sub                              '<- moved up
        End If                                    '<- moved up
    Next x
End Sub

Spot on. Works like a champ! Thanks for your wisdom and insight.

 

[mraycii]

when the PATH changes you need to define the value of PATH
Move the line
PATH = Range("F2").Value
to inside the loop and change it to: (should be the first line after your For r = ..... statement.
PATH = Range("F" & r).Value

Understand, and I believe this is what is happening in the post from Bosquedeguate. Thanks for your help just the same.

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.