Copying folder and contents to new directory using VBA

I am new to the form and new to using macros or VBA in Excel and was hoping that someone could help with a problem. I have created a list of folders and their file paths in Column A of a spreadsheet and would like to copy the folders and their contents to a new location shown in Column B. eg

g:\my documents\Folder 1 h:\Folder copy\Folder 1
g:\my documents\Folder 2 h:\Folder copy\Folder 2
g:\my documents\Folder 3 h:\Folder copy\Folder 3
g:\my documents\Folder 4 h:\Folder copy\Folder 4
g:\my documents\Folder 5 h:\Folder copy\Folder 5

I would like to do this as a macro so that all the folders and their contents are copied to the new location and any folders in Column A that are not found are ignored. As there are over 5,000 folders to be copied I would like it to run automatically until all folders that are found in Column A are copied to the appropriate location in Column B.

I would be most grateful if someone could assist me.

Here's a good place to start: http://www.rondebruin.nl/folder.htm

Jomili, thanks for your quick response and the link you provided. I am totally new to VBA and I can see how the routine on the other post will copy the folders. However, it seems to only work for one location to another and I need the macro to work its own way through the list (loop?), ignoring any folders it cannot find. Can you point me in the direction of how this could be achieved.

Thanks so much for the help you have already provided.

Can somebody help me modify this example macro like following:

I would look for the Project ID column B in excel from Target folder and move the DX-1000*.xls to the Destination.

Then it would loop until no more rows has data

Target Project ID Month Destination
C:\Dataretrivalfolder DX-1000 April C:\Projectfolder\DX-1000\April
C:\Dataretrivalfolder DX-1001 April C:\Projectfolder\DX-1001\April
C:\Dataretrivalfolder DX-1002 April C:\Projectfolder\DX-1002\April
C:\Dataretrivalfolder DX-1003 April C:\Projectfolder\DX-1003\April

Many Thanks,

EnzioL



Sub Copy_Certain_Files_In_Folder()
'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 FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String

FromPath = "C:\Users\Ron\Data" '<< Change
ToPath = "C:\Users\Ron\Test" '<< Change

FileExt = "*.xl*" '<< Change
'You can use *.* for all files or *.doc for word files

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

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
MsgBox "You can find the files from " & FromPath & " in " & ToPath

End Sub

Seymour100,
I'm so sorry I missed your last post! I've attached a workbook that I think will do what you want. It's based on the filepaths being in A and B, but I think you can easily change that to whatever suits you. The macro is shown below for anyone who wants it (it's just a minor derivation from Ron DeBruin's code).

EnzioL,
Please begin a new post of your own; it's considered poor form to hijack another thread (I know: I've done it before myself).

Please Login or Register  to view this content.

This is great...does anyone know how to slightly change this to create the destination folder if it does not exist?

Regards,