Create a new folder based on cell and copy list of file into created folder

[mattmar]

Greetings,

I am new in the VBA excel. I would like to ask to everyone.
I have a list of document with .pdf extension (in column A1:A20). I want to copy all documents from source folder (in my disk C to destination folder (in disk D. The destination folder is a new folder that named by cell value (B1 on other sheet).

'This code for copy files from the document list
 Sub copyfiles()

 Const sourcePath As String = "C:\Users\"  'source folder
 Const DestPath As String = "D:\Users\" 'how to change it with new folder that named is from cell B1 'destination folder
 Const ListAddress As String = "A1:A20"  'document list

' Write file list to array.
 Dim FileList As Variant: FileList = Sheet4.Range(ListAddress).Value

' 'Get' first file name.
 Dim FName As String: FName = Dir(sourcePath)
' 'Initiate' counter.
Dim i As Long
' Loop files in SourcePath.
Do While FName <> ""
' Check if file name of current file is contained in array (FileList).
If Not IsError(Application.Match(FName, FileList, 0)) Then
    ' Count file.
    i = i + 1
    ' Copy file.
    FileCopy sourcePath & FName, DestPath & FName
End If
' 'Get' next file name.
FName = Dir()
Loop

' Inform user.
Select Case i
Case 0: MsgBox "No files found", vbExclamation, "No Files"
Case 1: MsgBox "Copied 1 file.", vbInformation, "Success"
Case Else: MsgBox "Copied " & i & " files.", vbInformation, "Success"
End Select
End Sub

I try to make new folder based on cell value with this code, but I have no idea how to connect it with destination folder.

Dim startPath As String
Dim myName As String
startPath = "H:\Users\"
myName = ThisWorkbook.Sheets("Cover Page").Range("B1").Text      
If myName = vbNullString Then myName = "Nuovo"
Dim folderPathWithName As String
folderPathWithName = startPath & Application.PathSeparator & myName
If Dir(folderPathWithName, vbDirectory) = vbNullString Then
MkDir folderPathWithName
Else
MsgBox "Folder already exists"
Exit Sub
End

Is there anyone here can help me with the code to connect it? Thanks in advance for every help.

 

[mattmar]

Hi,

If you have same problem, I already solve this. you can find it in this link.

Make a new folder based on cell name and copy list of file into it

I am new in the VBA excel. I would like to ask to everyone. I have a list of document with .pdf extension (in column A1:A20). I want to copy all documents from source folder (in my disk C:) to

stackoverflow.com

have a good day

 

[Fluff]

@mattmar
Thanks for letting us know it's been resolved, but in future please supply links to any cross posts when you ask the question.
Please see Rule#13

 

[mattmar]

@Fluff
Will do. Thanks for reminder and sorry for inconvenience.

 

[Fluff]

Thanks