Private Const strName As String = "Сметный расчет.xlsx"
Private Const strNameL As String = "СМЕТА"

'вывод листа strNameL в файл strName
Sub ListOut()
     Dim str1 As String
          
         str1 = ThisWorkbook.Path & Application.PathSeparator
         Workbooks.Open Filename:=str1 & strName
          
         With Workbooks(strName)
         ThisWorkbook.Sheets(strNameL).Copy Before:=.Sheets(1)
         .Save
         .Close 0
     End With
End Sub

'вставка листа strNameL из книги strName
Sub getList()
     Dim str1 As String
          
         str1 = ThisWorkbook.Path & Application.PathSeparator
         Workbooks.Open Filename:=str1 & strName
          
         With Workbooks(strName)
         .Sheets(strNameL).Copy Before:=ThisWorkbook.Sheets(1)
         .Save
         .Close 0
     End With
End Sub

'генерация новой книги и вставка туда листа strNameL
Sub newBook()
Dim New_Wb As Workbook
Dim str1 As String
Dim strCurrFile As String
Const strNewBook As String = "out.xlsx" 'имя нового файла
      
     strCurrFile = ActiveWorkbook.Name 'имя текущего файла
     str1 = ThisWorkbook.Path & Application.PathSeparator
     Set New_Wb = Workbooks.Add
     With New_Wb
         .Activate
         Workbooks(strCurrFile).Sheets(strNameL).Copy Before:=.Sheets(1)
        .SaveAs (str1 & strNewBook)
        .Close
     End With
End Sub