Sub open_book()

Dim test_nmbr As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    Workbooks.Open Filename:= _
        "https://sharepoint.itms.ru/DocLib3/Номера%20тестов.xlsm?d=wdac482a1cde9486f921f68de80bdaa41"
       
    ActiveWorkbook.LockServerFile
        PosStr = Cells(1, 1).CurrentRegion.Rows.Count
        Cells(PosStr + 1, 1).Activate
        ActiveCell.FormulaR1C1 = "=""t_""&RIGHT(YEAR(TODAY()),2)&""_""&RIGHT(((TRIM(MID(SUBSTITUTE(RC[-1],""_"",REPT("" "",50)),50,50))&RIGHT(R[-1]C,LEN(R[-1]C)-FIND(""_"",R[-1]C,4)))*1),4)+1"
        ActiveCell.Value = ActiveCell.Value
        test_nmbr = ActiveCell.Value
        ActiveWorkbook.Close SaveChanges:=True
    
    With ThisWorkbook
        ActiveCell.Value = test_nmbr
    End With
    
 Application.ScreenUpdating = True
 Application.DisplayAlerts = True
 Application.Calculation = xlCalculationAutomatic
 
End Sub

Option Explicit

Private Const xKey = 1 'Столбец ключа
Private Const xValue = 5 'Столбец значения

Sub myVLOOKUP()
    Dim file1 As String
    file1 = ThisWorkbook.Path & "\Первый.xlsx"

    Dim file2 As String
    file2 = ThisWorkbook.Path & "\Второй.xlsx"

Sub Comment_Text()
On Error Resume Next
For Each R In ActiveSheet.UsedRange
    'R.Value = R.Value & R.Comment.Text  'добавляет примечания к данным ячейки
    'R.Value = R.Comment.Text  'меняет данные ячейки на текст примечания
    R.Value = R.CommentThreaded.Text
Next
Cells.ClearComments
End Sub