really ask for this help to finish this very long project, will be so grateful for any help please.
I'm trying to write a VBA code that executed by a commandbutton, the code will search for a folder called "library" next to the excel file, and inside that library the code will search for the last created Excel file and copy it and paste it with the name the user gave it in the commandbutton.
I've added 2 pictures that demonstrate what I'm trying to accomplish, and I've added a code not really related, the code is doing something different but I thought that it might help, the code copy paste an excel file located next to the file and paste it inside a folder, name it like the user type and create a row and create hyperlink . This code was created by JBeaucaire :
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"
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
Anchor:=.Offset(, -1), _