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

mattmar

New Member
Joined
Sep 14, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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).
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

mattmar

New Member
Joined
Sep 14, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

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


have a good day
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
@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

New Member
Joined
Sep 14, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@Fluff
Will do. Thanks for reminder and sorry for inconvenience.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,118,188
Messages
5,570,755
Members
412,340
Latest member
nikitesh95
Top