Option ExplicitDim arrSub ClickAndMove()Dim i As Long[a1].Formula = "=ToArray('" & ThisWorkbook.Path & "\[Книга2.xlsx]Лист1'!F6:P6)"Selection(1, 1).Resize(, 11).Value = arr[a1] = ""End SubPrivate Function ToArray(ref) arr = refEnd Function
Option ExplicitDim arrSub ClickAndMove()Dim i As Long, f As Stringf = GetFilePath 'этот путь можно прописать куда-нибудь в ячейкуi = InStrRev(f, "\"): f = Mid(f, 1, i) & "[" & Mid(f, i + 1)[a1].Formula = "=ToArray('" & f & "]Лист1'!F6:P6)"'для SelectionSelection(1, 1).Resize(, 11).Value = arr''или для последней незаполненной ячейки в столбце "О"'Cells(Rows.Count, 15).End(xlUp).Offset(1).Resize(, 11).Value = arr[a1] = ""End SubPrivate Function ToArray(ref) arr = refEnd FunctionPrivate Function GetFilePath(Optional ByVal Title As String = "Выбираем файл", Optional ByVal InitialPath As String) As StringGetFilePath = ""With Application.FileDialog(msoFileDialogFilePicker) .ButtonName = "Выбрать": .Title = Title: .InitialFileName = InitialPath: .Filters.Add "Excel", "*.xls*", 1 If .Show = -1 Then If .SelectedItems(1) <> "" Then GetFilePath = .SelectedItems(1)End WithEnd Function