Copying files to a newly created folder named from data within spreadsheet

[AndyEd]

I have the following code to create a folder at a location specified by the user,

openAt = "My computer:"

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose where to create the folder", 0, openAt)

On Error Resume Next
BrowseForFolder = ShellApp.Self.Path

MkDir (BrowseForFolder & "\" & Workbooks("Create folders & files").Worksheets("Tracker").Range("A2").value & _
"." & Right(Workbooks("Create folders & files").Worksheets("Tracker").Range("I2"), 4) & _
" - " & Workbooks("Create folders & files").Worksheets("Tracker").Range("E2").value)

I also have the following code which currently copies a named file into a specified folder within the DestinationPath string.

Dim SourcePath As String
Dim DestinationPath As String

SourcePath = "C:\TEMPLATE\1. Front sheet.xlsm"
DestinationPath = "C:\test\1. Front sheet.xlsm"
FileCopy SourcePath, DestinationPath

Would someone be so kind as to help me by explaining how I could copy the file into the folder created within the initial piece of code, all within the same subroutine, if possible.

Many thanks.

 

[John_w]

Try this macro:

Public Sub Create_Subfolder_and_Copy_File()

    Dim openAt As String, ShellApp As Object, BrowseForFolder As String
    Dim SourcePath As String
    Dim DestinationPath As String
    
    openAt = "My computer:"
    
    Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose where to create the folder", 0, openAt)
    If ShellApp Is Nothing Then
        MsgBox "User cancelled", vbInformation
        Exit Sub
    End If
    
    'On Error Resume Next
    BrowseForFolder = ShellApp.Self.Path
    
    With Workbooks("Create folders & files").Worksheets("Tracker")
        BrowseForFolder = BrowseForFolder & "\" & .Range("A2").Value & "." & Right(.Range("I2"), 4) & " - " & .Range("E2").Value
    End With
    
    MkDir BrowseForFolder
    
    SourcePath = "C:\TEMPLATE\1. Front sheet.xlsm"
    DestinationPath = BrowseForFolder & "\1. Front sheet.xlsm"
    FileCopy SourcePath, DestinationPath

End Sub

 

[AndyEd]

Thanks John. Apologies for the delay.