VBA that copy-paste a specific folder and name it as what the user was typing

netanel99

Board Regular
Joined
May 13, 2013
Messages
168
Hello all excel's Gurus and thank you so much for any sort of help.

I'm trying to write a code executed by commandbutton that will search a folder next to the file that called "projects and mega projects" and then search in the sub-folder a folder that called "template" and copy it and paste it there, giving it the name that the user has wrote , and then add a new row in the table and add a hyperlink in "click to view" that direct to this new folder created.

I've added pictures explaining what exactly I'm trying to achieve and I've added a code that does something else, but maybe it will help, this code is copy-paste a file called Template.xlsm, add a row, and hyperlink to this new file, it's a good code but it's not what I'm trying to achieve here, I'm trying to copy paste a folder.

THANK YOU SO MUCH FOR ANY HELP . !
I CANT BE GRATEFUL ENOUGH!


VBA Code:
Option Explicit

Private Sub CommandButton1_Click()
Dim StrategyName As String, strategyRow As Long, NewName As String 'JBeaucaire made this code.

StrategyName = Application.InputBox("Enter the new product/service name", "New product/service Name", Type:=2)
If StrategyName = "False" Then Exit Sub
On Error Resume Next
strategyRow = WorksheetFunction.Match(StrategyName, ThisWorkbook.Sheets("Products & Services Contents").Range("B:B"), 0)
If strategyRow > 0 Then
    MsgBox "This product/service already exists"
    Exit Sub
End If

MkDir ThisWorkbook.Path & "\StrategiesAuto"
MkDir ThisWorkbook.Path & "\StrategiesAuto" & "\" & StrategyName
NewName = ThisWorkbook.Path & "\StrategiesAuto" & "\" & StrategyName & "\StrategyFromTemplate-" & StrategyName & ".xlsm"
FileCopy ThisWorkbook.Path & "\StrategyFromTemplate.xlsm", NewName
With ThisWorkbook.Sheets("Products & Services Contents").Range("B" & Rows.Count).End(xlUp).Offset(1)
    .Value = StrategyName
    ActiveSheet.Hyperlinks.Add _
        Anchor:=.Offset(, -1), _
        Address:=NewName, _
        TextToDisplay:="Link"
    Workbooks.Open NewName
    
End With

End Sub


1.jpg


22.gif
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,141,128
Messages
5,704,446
Members
421,350
Latest member
jake9951

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top