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
Sure. Post the code that you are using now. 1 out of curiosity, 2 so I know where to put the additional lines of code.I'm using your original "single cell" macro duplicated couple of times with different values and it is working excellent. (in the table)
Could you please modify - if the source file is missing to put "0" automatically in that cell? is this could be done ?
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
Sure, you can. I’m just trying to get to work with what I have. (original code)
Data from closed workbooks need to be in a table.