Copy files based on excel value 1

[Nogi]

I'm a total newbie when it concerns VBA coding, so maybe one of you could help me out getting this script on the right track.
I have found the following code:

Sub Rectangle1_Click() 
    Dim n1 As String 
    Dim n2 As String 
    p = "C:\Documents and Settings\Desktop\" 
    n1 = p & "\" & "testdoc.doc" 
    n2 = p & "\" & "testdoc2.doc" 
    a = MoveFile(n1, n2) 
End Sub 
 
Function MoveFile(AFileName As String, aDestinationName As String) 
     'Note - Include full paths above
    If AFileName = "" Then Exit Function 
     'If testforfile(AFileName) < 1 Then Exit Function: ' this is a custom function to test if the file exists
    Set fs = CreateObject("Scripting.FileSystemObject") 
    Set f = fs.getfile(AFileName) 
    f.Copy aDestinationName 
     ' f.Delete: 'optional step
    fs = "" 
    f = "" 
End Function

What it does is copy a file from one location to another.

In my particular situation, i have an excelfile in which column B contains the names of folders.
The folders themselfs exist on a network location.

What i would like to accomplish is, that with a click on a button, the script reads all foldernames of Column B, and copies their corresponding folders on the network to the path of the excelfile.

So as example:
Cell B1= "S0003344", B2="S0007070", B3="S050505" etc.
With a click on a button, the script goes to the networklocation (//mynetwork/folders) and copies all folders of which the names are in the excel column, to the path of the excelfile.

Is there somebody who thinks he can help me out with this?

Thanks in advance

 

[comaboy]

This might help you get going...

Sub CopyAllInRange2005()
    Dim MyPath As String, MyName As String
    MyPath = Path ' You'll want to change this!
    For Each c In Range("B4:B56")'set this to your range
        FileCopy MyPath & "\Template.xls", MyPath & "\" & c.Value & ".xls"
    Next
    MsgBox "All Done!"
End Sub

 

[Nogi]

Hey thanks alot for your great help comaboy. Highly appriciated, and most of all, working! yeay!

again, thanks for your effords & time,

Nogi

 

[comaboy]

:-D