Selected Answer
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.