Sub Get_Value_From_Close_Book_Formula()
Dim sPath As String, sFile As String, sShName As String, sep_ As String
sShName = "Лист1"

With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Clear
.Filters.Add "Microsoft Excel files", "*.xls"
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path
If .Show = 0 Then Exit Sub
sep_ = Application.PathSeparator
sFile = Split(.SelectedItems(1), sep_)(UBound(Split(.SelectedItems(1), sep_))) ' "Êíèãà1.xls"
sPath = Replace(.SelectedItems(1), sFile, "") '"C:\Documents and Settings\"
End With

With Range("H6:AS100")
.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A6"
.Value = .Value
End With
End Sub

Sub ert()
Range("A1").CurrentRegion.Copy Workbooks("Другая_книга.xls").Sheets(1).Range("H6")
End Sub

.Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1"

With Range("C2:С101")

Option Explicit

Sub Get_Value_From_Book()
       Dim sFile As String, sh As Worksheet, ac As Long

       With Application.FileDialog(msoFileDialogFilePicker)
           .Filters.Clear
           .Filters.Add "Microsoft Excel files", "*.xls"
           .AllowMultiSelect = False
           .InitialFileName = ThisWorkbook.Path
           If .Show = 0 Then Exit Sub
           sFile = .SelectedItems(1)
       End With

       Set sh = ActiveWorkbook.ActiveSheet

       With Application
           'отключаем обновление экрана - это убыстрит работу макроса
           .ScreenUpdating = False
           'отключаем события книги
           .EnableEvents = False
           'включаем ручной пересчёт формул - это убыстрит работу макроса
           ac = .Calculation: .Calculation = xlCalculationManual

           With GetObject(sFile).Sheets(1)
               .[A1].CurrentRegion.Copy sh.[H6]
               .Parent.Close False
           End With

           'возвращаем назад всё отключенное/изменённое
           .ScreenUpdating = True
           .EnableEvents = True
           .Calculation = ac
       End With

End Sub

Sub ttt()
ActiveSheet.UsedRange.Select
With ActiveSheet.UsedRange
.Resize(.Rows.Count - 6).Offset(6).Select
End With
End Sub

Sub Get_Value_From_Book()
        Dim sFile As String, sh As Worksheet, ac As Long

        With Application.FileDialog(msoFileDialogFilePicker)
            .Filters.Clear
            .Filters.Add "Microsoft Excel files", "*.xls"
            .AllowMultiSelect = False
            .InitialFileName = ThisWorkbook.Path
            If .Show = 0 Then Exit Sub
            sFile = .SelectedItems(1)
        End With

        Set sh = ActiveWorkbook.ActiveSheet
          
        Range("H6:AX" & Cells(Rows.Count, 1).End(xlUp).Row).Clear

        With Application
            'отключаем обновление экрана - это убыстрит работу макроса
            .ScreenUpdating = False
            'отключаем события книги
            .EnableEvents = False
            'включаем ручной пересчёт формул - это убыстрит работу макроса
            ac = .Calculation: .Calculation = xlCalculationManual

            With GetObject(sFile).Sheets(1).UsedRange
                .Resize(.Rows.Count - 5).Offset(5).Copy sh.[H6]
                '.Parent.Close False
            End With

            'возвращаем назад всё отключенное/изменённое
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = ac
        End With

End Sub

With GetObject(sFile).Sheets(1).[A1].CurrentRegion
              .Resize(.Rows.Count - 5).Offset(5).Copy sh.[H6]
              '.Parent.Close False
          End With

With GetObject(sFile).Sheets(1).[A1].CurrentRegion         
                     .Resize(.Rows.Count - 5).Offset(5).Select 'Copy sh.[H6]   '<-- ВОТ НА ЭТОЙ
                     '.Parent.Close False         
                 End With

    With GetObject(sFile).Sheets(1)
          With .[A1].CurrentRegion
              .Resize(.Rows.Count - 5).Offset(5).Copy sh.[H6]
          End With
          .Parent.Close False
      End With