Move files to new folder as per list in excel

[atuljadhavnetafim]

Dear Expert

I have 2000+ PDF files in one folder "Current Location" now I want to move this in 3 folders which mentioned in "Desired Location".
i need to copy pdf files to desired location, the original should be in current location as it is.

can any one provide macro for that

File Name	Current Location	Desired Location
BNCPK8159E_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\SBU SOUTH\
ADUPP6015B_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\HEAD OFFICE\
AWVPS9707M_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\HEAD OFFICE\
ARCPS3364A_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\Chennai-Factory\
ABGPD3118D_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\HEAD OFFICE\
AAGFH7457J_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\HEAD OFFICE\
ADWPV3973C_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\HEAD OFFICE\
ALWPM6372J_Q4_2020-21.pdf	C:\Users\ni****p\Desktop\TDS Certificate All	C:\Users\ni****p\Desktop\TDS Certificate All\SBU SOUTH\

Thanks in advance.

 

[shinigamilight]

Enable microsoft scripting runtime in references.
Code is untested

Sub bye()
        
        Dim FSO As New FileSystemObject
        Dim des, sour As String
        Dim k As Integer
        Dim lr As Long
        lr = Range("A1").End(xlDown).Row
        
        For k = 2 To lr
                sour = Range("B" & k) & "\" & Range("A" & k)
                des = Range("C" & k)
                FSO.CopyFile sour, des
       Next k
       
        
        
End Sub

 

[atuljadhavnetafim]

Hi,

below error show

 

[atuljadhavnetafim]

another error

 

[shinigamilight]

another error

View attachment 82189

How do I use FileSystemObject in VBA?

Is there something that I need to reference? How do I use this: Dim fso As New FileSystemObject Dim fld As Folder Dim ts As TextStream I am getting an error because it does not recognize these ob...

stackoverflow.com

 

[atuljadhavnetafim]

Already make changes in reference but still getting permission error

 

[shinigamilight]

Hi,

below error show

View attachment 82183

In column A there's no extension for the filename. If it still doesn't work then there's some permission conflict. Are you the admin of the PC?
Try this code now.

Sub bye()
        
        Dim FSO As object
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Dim des, sour As String
        Dim k As Integer
        Dim lr As Long
        lr = Range("A1").End(xlDown).Row
        
        For k = 2 To lr
                sour = Range("B" & k) & "\" & Range("A" & k)
                des = Range("C" & k) & "\"
                FSO.CopyFile sour, des
       Next k
       
        
        
End Sub

 

[atuljadhavnetafim]

Yes, thanks, it's working,
i update file extension and use new code and it's working fine

Thanks a lot, it's save lot's of time.