Create a new folder based on cell and copy list of file into created folder
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).
Code:
'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.
Code:
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.
