New Folders From Selected Range (Cells) With A Workbook Copied To Each

[Oakwoodbespoke]

Hi,

I'm trying to do the following:

I have a list of job numbers with their relevant site addresses (I have made them as one with CONCATENATE in Column E)

I have managed to get things to work so you can select cells in Column E and it will create new folders within a Folder named "New Folder 2" on the desk top

What I would like it to do is once the new folders are created it copies a Excell workbook from a specified location and adds it to each newly created folder.

Thanks in advance for any help




Sub MakeFolders()
Dim dirName As String
Dim selectedRange As Range
Dim cell As Range
Dim i As Long

' Prompt user to select a range of cells
Set selectedRange = Application.InputBox("Select a range of cells:", "Select Range", Type:=8)

' Check if a range was selected
If Not selectedRange Is Nothing Then

' Create folders based on cell values
On Error Resume Next ' Enable error handling
For Each cell In selectedRange
dirName = cell.Value
MkDir "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName

Next cell
On Error GoTo 0 ' Reset error handling

MsgBox "Folders have been created successfully!", vbInformation
Else
MsgBox "No range selected. Operation cancelled.", vbInformation
End If
End Sub

 

[saboh12617]

Hello,
I think you are looking for this instruction FileCopy statement (VBA)

Be careful to re-add the file name at the end of the instruction.

Dim filePath as String: filePath = "c:\........\test.xlsx"
dim fileName as String: fileName = "test.xlsx"
FileCopy filePath, "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName & fileName

 

[Oakwoodbespoke]

Dim filePath as String: filePath = "c:\........\test.xlsx" dim fileName as String: fileName = "test.xlsx" FileCopy filePath, "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName & fileName

Ive added the code and its not quite working

I selected 3 cells and it created 3 new folders that are named correctly, but it has renamed the excell workbook and copied them into New Folder 2 instead of each new folder

Sub MakeFolders()
Dim dirName As String
Dim selectedRange As Range
Dim cell As Range
Dim i As Long

' Prompt user to select a range of cells
Set selectedRange = Application.InputBox("Select a range of cells:", "Select Range", Type:=8)

' Check if a range was selected
If Not selectedRange Is Nothing Then

' Create folders based on cell values
On Error Resume Next ' Enable error handling
For Each cell In selectedRange
dirName = cell.Value
MkDir "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName
Dim filePath As String: filePath = "c:\Users\xxusernamexx\Desktop\test.xlsx"
Dim fileName As String: fileName = "test.xlsx"
FileCopy filePath, "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName & fileName

Next cell
On Error GoTo 0 ' Reset error handling

MsgBox "Folders have been created successfully!", vbInformation
Else
MsgBox "No range selected. Operation cancelled.", vbInformation
End If
End Sub


Thanks so much for your help

 

[Oakwoodbespoke]

worked it out

FileCopy filePath, "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName & "\" & fileName

the back slash was missing between dirName & "\" & fileName

 

[Oakwoodbespoke]

thanks so much for your help

 

[saboh12617]

Oh yes my bad stupid mistake I'm sorry... Glad you figured it out!
Thanks for the feedback.

 

[Oakwoodbespoke]

Oh yes my bad stupid mistake I'm sorry... Glad you figured it out!
Thanks for the feedback.

 

[saboh12617]

Hi saboh12617

Thanks again for your help,

Out of interest would it be possible to rename the file that it is copying into each new folder (in this case Test) with the same folder name such as this

New folder example
2699 (45 Tedhot)

The excel workbook that's copying into this folder would be renamed from "Test" to the following
2699 (45 Tedhot) taken from the folder name + Account Spreadsheet after it

So the file name would be

2699 (45 Tedhot) Account Spreadsheet

Please example image

Many thanks again

Well if I understood correctly, the solution is very straightforward:

You already have the folder's name it is the variable dirName in your code.

So then, you just need to replace the filename like so

Dim fileName As String: fileName = dirName & " Account Spreadsheet" & ".xlsx"

So at the end, the code should look like this

' folder creation
MkDir "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName

' path to file to copy
Dim filePath As String: filePath = "c:\Users\xxusernamexx\Desktop\test.xlsx"

' new file name
Dim fileName As String: fileName = dirName & " Account Spreadsheet" & ".xlsx"

' copy
FileCopy filePath, "c:\Users\xxusernamexx\Desktop\New Folder 2\" & dirName & "\" & filename

However, for more advanced file management in VBA, you should take a look at the FileSystemObject object | Microsoft Learn. It is very powerful, and simplifies the syntax for files and folder edition (extensions, fullpath, parent folder, etc.)

 

[Oakwoodbespoke]

Hi saboh12617

Thanks again for your help,

Out of interest would it be possible to rename the file that it is copying into each new folder (in this case Test) with the same folder name such as this

New folder example
2699 (45 Tedhot)

The excel workbook that's copying into this folder would be renamed from "Test" to the following
2699 (45 Tedhot) taken from the folder name + Account Spreadsheet after it

So the file name would be

2699 (45 Tedhot) Account Spreadsheet

Please example image

Many thanks again

 

[Oakwoodbespoke]

works perfectly , thanks so much that's certainly going to save me a lot of time,

Thanks so much for your help