Sub GetRangeFromClosedWorkbook()
'
Dim FileNameRow As Long
Dim FirstBlankCellRowInColumnRange As Long
Dim LastRowUsedInColumn As Long
Dim Cell As Range
Dim ColumnRange As Range
Dim DestinationSheet As String
Dim ResultColumn As String
Dim SourceDirectory As String
Dim SourceFileAddressColumn As String
Dim SourceFileAddressRow As String
Dim SourceFileName As String
Dim SourceRange As String
Dim SourceSheet As String
'
DestinationSheet = "Sheet1" ' <--- Set this to the sheet name used to store values from the closed workbook
SourceFileAddressColumn = "A" ' <--- Set this to the column used for Source File Names
SourceFileAddressRow = "5" ' <--- Set this to the start row number used for Source File Names
ResultColumn = "B" ' <--- Set the column to store results in
SourceRange = "$C$38" ' <--- Set this to the range in the closed workbook to get data from
SourceDirectory = "C:\Test\Data\" ' <--- Set this to the directory of the closed workbooks
'
'
LastRowUsedInColumn = Sheets(DestinationSheet).Range(SourceFileAddressColumn & Rows.Count).End(xlUp).Row ' Find Last Used Row in Column
'
' Prep to check for blanks in column range, if so, set last row to the last row of the first section
Set ColumnRange = Sheets(DestinationSheet).Range(SourceFileAddressColumn & SourceFileAddressRow & ":" & SourceFileAddressColumn & LastRowUsedInColumn + 1)
'
For Each Cell In ColumnRange
If Cell.Value = vbNullString Then ' If blank cell found then ...
FirstBlankCellRowInColumnRange = Cell.Row ' Save the row number of the first blank found in the column range
Exit For ' Exit For loop
End If
Next
'
LastRowOfFileNames = FirstBlankCellRowInColumnRange - 1 ' Back up one row number
'
For FileNameRow = SourceFileAddressRow To LastRowOfFileNames ' Range of column to loop through
SourceFileName = SourceFileAddressColumn & FileNameRow ' Set Address to use for the file name of closed workbook
SourceSheet = SourceFileName ' Set the sheet name to use for the closed workbook same as file name
Sheets(DestinationSheet).Range(ResultColumn & FileNameRow).Formula = "='" & SourceDirectory & "[" & Sheets(DestinationSheet).Range(SourceFileName) & ".xlsx]" & SourceSheet & "'!" & SourceRange ' Set Formula to range
'
' Remove formula from range, leave just the resulting value found in closed workbook
Sheets(DestinationSheet).Range(ResultColumn & FileNameRow).Value = Sheets(DestinationSheet).Range(ResultColumn & FileNameRow).Value
Next
'
MsgBox "Done" ' Alert user that range from closed workbook has been loaded to sheet
End Sub
