Selected Answer
Squishy
Don's tutorial was meant for files with similar formats (same column headers dta in same columns. In the file attached, I've modified the code so it:
- adds a blank row per worksheet searched
- puts the worksheet name and the file name/path in that row (so you know where the data is from)
- put the headers from that sheet in the next row then the all the found data (as the tutorial code did).
The code extract below shows the code portions I've changed. In essence, I:
- changed the dataRowStart constant from 2 to 1 (so it gets the first row of each sheet)
- made the maximum array size 2 rows larger to allow for the new filename and header rows
- re-used the counter k to loop through the dataRowStart row (i.e. the headers)
- put any found data matches in two rows lower (than the tutorial code).
See changes in bold below:
Sub Data_Search(wbMaster As Workbook, wbSlave As Workbook)
' TeachExcel.com
....
'Data table information
dataColumnStart = 1
dataColumnEnd = 15
dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)
dataRowStart = 1
dashboardDataColumnStart = 2 ' Column for the data on the dashboard
....
'Increase size of array that will hold the data to display to its max possible size for the current worksheet.
ReDim datatoShowArray(1 To UBound(dataArray, 1) + 2, 1 To UBound(dataArray, 2) )
'Row increment for the final data array (datatoShowArray).
j = 1
'Loop through the headers for each file
For k = 1 To UBound(dataArray, 2)
'Add blank row...
datatoShowArray(1, k) = ""
' then path/ file /sheet name...
datatoShowArray(1, 1) = "From file: " & wbSlave.Path & "\" & wbSlave.Name & " - " & ws.Name
'then header values to the array to put into the Dashboard.
datatoShowArray(2, k) = dataArray(1, k)
Next k
'Loop through the rows in the data range.
.
......
'MATCH FOUND! Now do something!
'Loop through the columns in the data range so can get their values.
For k = 1 To UBound(dataArray, 2)
'Add values to the array that will be used to put data into the Dashboard.
datatoShowArray(j + 2, k) = dataArray(i, k)
Next k
....
Hope this works for you.