In Fast Search Entire Excel Workbook & Return All Results into a Dashboard

Nigel

In the modified file attached, I've changed one variable to say which column (rather than row) the data should start from. I then let the macro gather data as before. Once that's done (for a worksheet), the macro finds the next free column in row 10 and pastes the same array datatoShowArray but it's (perhaps sneakily) transposed using the function:

Application.WorksheetFunction.Transpose(datatoShowArray)

The changes to  macro are in bold in the extract below (full code in the file):

Sub Data_Search()
' TeachExcel.com

.....
...... << code unchanged>>
......
......          
dashboardDataRowStart = 10 ' Row for the data on the dashboard
......
...... << other code unchanged>>
......
......          
        Next i

        'Find next empty column in the dashboard.
        nextColumn = dashboard.Cells(dashboardDataRowStart, Columns.Count).End(xlToLeft).Column + 1

        'Put data into the dashboard.
        'Format = Range(Cells(1,1),Cells(2,2)).Value = datatoShowArray
        dashboard.Range(Cells(dashboardDataRowStart, nextColumn), Cells(dashboardDataRowStart + dataColumnWidth, nextColumn + UBound(datatoShowArray, 1) - 1)).Value = Application.WorksheetFunction.Transpose(datatoShowArray)

    End If

'Go to the next worksheet.
Next ws

'Application.ScreenUpdating = True 'Turn it back on at the end of the macro!

End Sub

I also modified the data to clear the dashboard, as follows (change in bold):

Sub Clear_Data()

'Dashboard sheet
Set dashboard = Sheets("Dashboard")

'Data table information
dashboardDataColumnStart = 3 ' Column for the data on the dashboard
dashboardDataRowStart = 10 ' Changed for vertical headers

dashboard.Range(dashboard.Cells(dashboardDataRowStart, dashboardDataColumnStart), dashboard.Cells(Rows.Count, Columns.Count)).Clear

End Sub

Hope this works for you.

Is there any way the "dataArray" can be filled using currentregion and other stuff remains same. I am not expert but just a thought came in my mind. Would it become simpler or more complicated than current code. Also I am having dificulty in understanding the last portion of the below expression, the one that picks the last value of the sheet.

dataArray = ws.Range(ws.Cells(dataRowStart, dataColumnStart), ws.Cells(ws.Cells(Rows.Count, dataColumnStart).End(xlUp).Row, dataColumnEnd)).Value

dataArray = ws.Range(ws.Cells(dataRowStart, dataColumnStart), ws.Cells(ws.Cells(Rows.Count, dataColumnStart).End(xlUp).Row, dataColumnEnd)).Value