How to copy a file into folders that are created based on a list?

[Guest]

I'm currently using this sub to create new folders based on an Excel that I
have:

Sub MakeFolder()
For Each Cell In Range("C1:C43")
MkDir (Cell.Text)
Next Cell
End Sub


What I'd like to do is to copy a file into each of these newly created
folders. Say for example, I want to copy C:\MyDocs\template.doc into each
and every one of these new folders, is there code I can use to do that? The
code I've found referencing such a copy an paste file function seems to
require an absolute file destination path (e.g.
C:\MyDocs\newcreatedfolder\template.doc) and I'm not quite sure how to
reference that?

Thanks in advance!

 

[Guest]

Following up:
I changed the code to the following:

Sub MakeFolder()

Dim strSourcePath As String
Dim strSourceFile As String
Dim strTargetPath As String
Dim strTargetFile As String
Dim folName As String
Dim oFS As Object
Dim oFile As Object

For Each Cell In Range("C2:C4")
On Error Resume Next
folName = Cell.Text ' This sets the folder name as whatever is in
the current cell

MkDir folName ' This makes the new directory

strSourcePath = "c:\MyDocs\Testing\"
strSourceFile = "DocIWant.DOC"

strTargetPath = ActiveWorkbook.Path & "\" & folName & "\"
strTargetFile = "DocIWant.DOC"

strSource = strSourcePath & strSourceFile
strTarget = strTargetPath & strTargetFile

Set oFS = CreateObject("Scripting.FileSystemObject")
Set oFile = oFS.CopyFile(strSource, strTarget)
Set oFile = Nothing
Set oFS = Nothing

Next Cell
End Sub

And it seems to perform what I want it to do, except I needed to put in an
"On Error Resume Next" because it kept giving me a Runtime Error 424, Object
Required warning each time I tried to run it. The "On Error Resume Next" is
just a bandaid so I can finish running the script and create the remaining
folders (after the first row) and put the copied file in each of the folders.
I have no idea what the Error refers to, since it seems to perform the MkDir
and File.Copy functions just fine. If anyone has any clue, I'd appreciate it!

Thanks again!!