Displaying table information for visual clarity in a large area

[ForkliftCertified]

I'm somewhat new to excel/VBA, primarily a C#/python coder and I've been shoehorned into a position I'm not sure of what to do. So I was tasked with creating a spreadsheet that can be put up onto a large monitor for quickly displaying current jobs being worked on in my lab.

It uses data connections to pull table data from 3 other sheets, puts them all into a single table before sorting it by worksite and planned end date and then places all the information from single rows of this table across large...charts of data? It's so that the upper management who don't know exactly what's going on can just see what's going on at a glance without having to go through long meetings explaining everything.

I'm at a loss trying to figure out how best to do this and was hoping I could get a little bit of help or an example. I don't have access to excel at home but I more or less remember some of the VBA code as follows as well as roughly what the managers want me to make.

>>> use code - tags <<<

With ws
                    .Range(.Cells(cRow, cCol), .Cells(cRow + 8, cCol + 7)).VerticalAlignment = xlTop
                    .Range(.Cells(cRow, cCol), .Cells(cRow + 8, cCol + 7)).HorizontalAlignment = xlLeft
                    .Range(.Cells(cRow, cCol), .Cells(cRow + 8, cCol + 7)).WrapText = True
       
                    .Cells(cRow, cCol).Value = "Area / Manager" + vbNewLine & DirArray(rowTMP, 1) + vbNewLine & DirArray(rowTMP, 2)
                    .Range(.Cells(cRow, cCol), .Cells(cRow + 2, cCol + 1)).Merge
       
                    .Cells(cRow, cCol + 2).Value = DirArray(rowTMP, 4)
                    .Range(.Cells(cRow, cCol + 2), .Cells(cRow + 2, cCol + 2)).Merge
       
                    .Cells(cRow, cCol + 3).Value = DirArray(rowTMP, 5)
                    .Range(.Cells(cRow, cCol + 3), .Cells(cRow + 2, cCol + 3)).Merge
       
                    .Cells(cRow, cCol + 4).Value = DirArray(rowTMP, 6)
                    .Range(.Cells(cRow, cCol + 4), .Cells(cRow + 2, cCol + 4)).Merge
       
                    .Cells(cRow, cCol + 5).Value = DirArray(rowTMP, 10) + vbNewLine & DirArray(rowTMP, 11)
                    .Range(.Cells(cRow, cCol + 5), .Cells(cRow + 2, cCol + 5)).Merge
       
                    .Cells(cRow, cCol + 6).Value = DirArray(rowTMP, 9)
                    .Range(.Cells(cRow, cCol + 6), .Cells(cRow + 2, cCol + 7)).Merge
       
                    .Cells(cRow + 3, cCol).Value = DirArray(rowTMP, 3)
                    .Range(.Cells(cRow + 3, cCol), .Cells(cRow + 5, cCol + 2)).Merge
       
                    .Cells(cRow + 3, cCol + 3).Value = "start date" + vbNewLine & Format(CDate(DirArray(rowTMP, 7)), "mm/dd/yyyy")
                    .Range(.Cells(cRow + 3, cCol + 3), .Cells(cRow + 5, cCol + 3)).Merge
       
                    .Cells(cRow + 3, cCol + 4).Value = "end date" + vbNewLine & Format(CDate(DirArray(rowTMP, 8)), "mm/dd/yyyy")
                    .Range(.Cells(cRow + 3, cCol + 4), .Cells(cRow + 5, cCol + 4)).Merge

                    .Cells(cRow + 3, cCol + 6).Value = DirArray(rowTMP, 14)
                    .Range(.Cells(cRow + 3, cCol + 6), .Cells(cRow + 5, cCol + 6)).Merge

                    .Cells(cRow + 3, cCol + 7).Value = DirArray(rowTMP, 15)
                    .Range(.Cells(cRow + 3, cCol + 7), .Cells(cRow + 5, cCol + 7)).Merge

                    .Cells(cRow + 6, cCol).Value = DirArray(rowTMP, 12)
                    .Range(.Cells(cRow + 6, cCol), .Cells(cRow + 7, cCol + 5)).Merge
End With

This is some of the code I remember, where rowTMP is taken from a For loop that iterates through each row of the table. It also sets it up so that each iteration changes the cRow and cCol variables to use them as starting points for cell merging. It ends up in 2 columns of the data and gets displayed on a portrait tilted
monitor. The following is a rough mock up of what they wanted to see in the early stages before continuously adding more and more things they wanted displayed.




Any help would be appreciated. No access to excel at home(yet) but I can open it up at work. VBA isn't a requirement, it's just how my monkey brain wanted to go about tackling the problem

 

[Peter Bartholomew]

If you have access to Excel 365 you could start populating your array of reports by collecting data together to build a single instance of the report (one of your boxes with 'Data' and 'More data' in the header row). Once you have one instance of a report, you could use REDUCE/VSTACK to generate a column of such reports, one for each record of your source data. The output would actually be a single array, so no merged cells!