Ideas about how to produce reports from a database


Active Member
Sep 11, 2017
Hello everybody.

I've just taken charge of the database you can see in this link.

From this db, updated day by day (see column N), I have to produce some reports.

First of all I want to specify that my predecessor used to manage the reports with formulas, for this reason I've inherited a too heavy workbook (I think it will blow up sooner or later). So, I have to change strategy.

The report framework is usually with an horizontal axes as a timeline (based on column N), and a vertical axis with the requested information.
For instance, you can see one of them in the link below.

At the moment, I've create an array to store the database.

Sub database()

Dim database As Worksheet
    Set database = Sheets("Database")

Dim Ary As Variant, Tmp As Variant, Cols As Variant
Dim r As Long, c As Long

Dim lr As Long
    lr = database.Cells(Rows.Count, "A").End(xlUp).Row

'Si definisce l'area della tabella su cui effettuare la lavorazione
 With database.Range("A2:U" & lr)
      Ary = .Value2    
End With

   ReDim Cols(1 To UBound(Ary, 2) - 1)
   For c = 2 To UBound(Ary, 2)
      Cols(c - 1) = c
   Next c


How can I perform the task?

Considering the reports analised once per month, I can also produce the entire report when requested, even if I have to wait some minutes.

Or maybe a pivot strategy can fullfill the needs, without any elaboration with vba?

Thank you in advance for your contributions.

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.


Well-known Member
Jul 15, 2016

I would say it would depend what your comfortable with maintaining. Pivot tables make it easy to slice & filter data if setup correctly and that would be the same for formulas & VBA.

From the look of your data I would probably go with formula extraction if pivot tables didn't give me the result and easier to troubleshoot.

Watch MrExcel Video

Forum statistics

Latest member
Mohan Kumar

This Week's Hot Topics