Sub Obnovit_svyazi()
    Filename = "S:\FILES\УТО\ОТЧЕТЫ 2016 года\Сахаэнерго ТЭП за 2016.xlsx"
    Workbooks.Open Filename, False, True
    Application.ScreenUpdating = False
    Windows("Сахаэнерго ТЭП за 2016.xlsx").Visible = False
    Workbooks("Сахаэнерго ТЭП за 2016.xlsx").Close (False)
End Sub

Option Explicit


Sub Get_Value_1kvartal()
    Dim sPath As String, sFile As String, sShName As String
    sPath = "S:\FILES\УТО\ОТЧЕТЫ 2015 года\"
    sFile = "Сахаэнерго ТЭП за 2015.xlsx"
    sShName = "1 квартал"

    Application.DisplayAlerts = 0
     
'---------------------------------УГОЛЬ--------------------------
With Range("AB911")
        .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "AR117"
        .Value = .Value
End With
With Range("AC911")
        .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "AS117"
        .Value = .Value
End With
With Range("AA1373")
        .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "S179" ' - и таких строк около 150 штук. =)))
        .Value = .Value
End With

Option Explicit
Dim arr

Sub ListCopy()
UserForm1.Show
End Sub

Sub GetValue(sPath As String, sFile As String)
With Cells(1, 1)
    .Formula = "=GetExtData('" & sPath & "[" & sFile & "]1.10'!A1:Z1378)" ' 1.10 - лист загрузки данных, A1:Z1378 - загружаемый диапазон.
    .Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End With: arr = Empty
End Sub

Function GetExtData(Ref)
arr = Ref
End Function