It is 16:45 on a Friday afternoon. You open five different Excel files, copy data from each one, paste it into the report template, format the headings, add charts, check the totals. Two hours later you can finally send the week’s report.

Next Friday you do exactly the same thing again. And the week after that. 104 hours a year on the same repetitive task.

I helped a finance manager automate exactly this. Now the report takes 3 minutes to generate with the click of a button. The same report, the same quality, 117 times faster.

In this guide we build a complete reporting system step by step. You get ready-made code and detailed explanations. When you are done you can adapt the system to your own reports.

What we are going to build

An automated reporting system that: 1. Imports data from several source files 2. Compiles and calculates key figures 3. Creates a formatted report with a standard layout 4. Generates charts automatically 5. Saves the report with a date-stamped file name 6. Sends the report by email (optional)

All of this with a single press of a button.

Preparation

File structure

Create the following folder structure:

C:\Rapporter\
├── Källdata\
│   ├── Försäljning.xlsx
│   ├── Kostnader.xlsx
│   └── Budget.xlsx
├── Rapportmall.xlsm
└── Genererade rapporter\

Rapportmall.xlsm is the file where we put all the VBA code. The generated reports end up in the subfolder.

Structure of the source files

Each source file needs to have its data in a predictable structure:

Försäljning.xlsx, sheet “Data”:

Rad 1: Datum | Produkt | Antal | Belopp
Rad 2+: 2026-01-15 | Produkt A | 10 | 15000

Kostnader.xlsx, sheet “Data”:

Rad 1: Datum | Kategori | Belopp
Rad 2+: 2026-01-15 | Personal | 125000

Budget.xlsx, sheet “Data”:

Rad 1: Månad | Försäljning Budget | Kostnad Budget
Rad 2+: 2026-01 | 500000 | 350000

Adapt it to your actual data structure – the principles are the same.

Structure of the report template

Rapportmall.xlsm consists of three sheets:

1. “Import” – The raw data is imported here (hidden from end users)

2. “Beräkningar” – All calculations and key figures are made here (hidden)

3. “Rapport” – The formatted report that becomes visible

Step 1: Import data from source files

We start by fetching data from the three source files.

The code for the import

Sub ImporteraData()
    Dim wbKälla As Workbook
    Dim wsKälla As Worksheet
    Dim wsImport As Worksheet
    Dim sistaRad As Long
    Dim källSökväg As String

    källSökväg = "C:\Rapporter\Källdata\"

    ' Rensa tidigare import
    Set wsImport = ThisWorkbook.Worksheets("Import")
    wsImport.Cells.Clear

    ' Importera försäljning
    Set wbKälla = Workbooks.Open(källSökväg & "Försäljning.xlsx", ReadOnly:=True)
    Set wsKälla = wbKälla.Worksheets("Data")

    sistaRad = wsKälla.Cells(wsKälla.Rows.Count, "A").End(xlUp).Row

    ' Kopiera rubriker och data till kolumn A-D
    wsKälla.Range("A1:D" & sistaRad).Copy wsImport.Range("A1")

    wbKälla.Close SaveChanges:=False

    ' Importera kostnader
    Set wbKälla = Workbooks.Open(källSökväg & "Kostnader.xlsx", ReadOnly:=True)
    Set wsKälla = wbKälla.Worksheets("Data")

    sistaRad = wsKälla.Cells(wsKälla.Rows.Count, "A").End(xlUp).Row

    ' Kopiera till kolumn F-H (lämnar E tom som separator)
    wsKälla.Range("A1:C" & sistaRad).Copy wsImport.Range("F1")

    wbKälla.Close SaveChanges:=False

    ' Importera budget
    Set wbKälla = Workbooks.Open(källSökväg & "Budget.xlsx", ReadOnly:=True)
    Set wsKälla = wbKälla.Worksheets("Data")

    sistaRad = wsKälla.Cells(wsKälla.Rows.Count, "A").End(xlUp).Row

    ' Kopiera till kolumn J-L
    wsKälla.Range("A1:C" & sistaRad).Copy wsImport.Range("J1")

    wbKälla.Close SaveChanges:=False

    MsgBox "Import klar!", vbInformation
End Sub

What happens here?

  • Opens each source file in ReadOnly mode (prevents them from being changed by mistake)
  • Copies all the data to the Import sheet
  • Closes the source file without saving
  • Repeats for each source file

Tip: Put Application.ScreenUpdating = False at the beginning and = True at the end to make it faster.

Step 2: Calculate key figures

Now that all the data is in the Import sheet, we can calculate key figures.

The code for the calculations

Sub BeräknaNyckeltal()
    Dim wsImport As Worksheet
    Dim wsBeräkning As Worksheet
    Dim sistaRadFörsäljning As Long
    Dim sistaRadKostnad As Long
    Dim totFörsäljning As Double
    Dim totKostnad As Double
    Dim aktuellMånad As String

    Set wsImport = ThisWorkbook.Worksheets("Import")
    Set wsBeräkning = ThisWorkbook.Worksheets("Beräkningar")

    wsBeräkning.Cells.Clear

    ' Aktuell månad för filtrering
    aktuellMånad = Format(Date, "yyyy-mm")

    ' Hitta sista raderna
    sistaRadFörsäljning = wsImport.Cells(wsImport.Rows.Count, "A").End(xlUp).Row
    sistaRadKostnad = wsImport.Cells(wsImport.Rows.Count, "F").End(xlUp).Row

    ' Summera försäljning för aktuell månad
    Dim i As Long
    totFörsäljning = 0

    For i = 2 To sistaRadFörsäljning
        If Format(wsImport.Cells(i, 1).Value, "yyyy-mm") = aktuellMånad Then
            totFörsäljning = totFörsäljning + wsImport.Cells(i, 4).Value
        End If
    Next i

    ' Summera kostnader för aktuell månad
    totKostnad = 0

    For i = 2 To sistaRadKostnad
        If Format(wsImport.Cells(i, 6).Value, "yyyy-mm") = aktuellMånad Then
            totKostnad = totKostnad + wsImport.Cells(i, 8).Value
        End If
    Next i

    ' Skriv resultat till Beräkningar-bladet
    wsBeräkning.Range("A1").Value = "Nyckeltal"
    wsBeräkning.Range("A2").Value = "Försäljning"
    wsBeräkning.Range("B2").Value = totFörsäljning
    wsBeräkning.Range("B2").NumberFormat = "#,##0"

    wsBeräkning.Range("A3").Value = "Kostnader"
    wsBeräkning.Range("B3").Value = totKostnad
    wsBeräkning.Range("B3").NumberFormat = "#,##0"

    wsBeräkning.Range("A4").Value = "Resultat"
    wsBeräkning.Range("B4").Formula = "=B2-B3"
    wsBeräkning.Range("B4").NumberFormat = "#,##0"

    wsBeräkning.Range("A5").Value = "Marginal %"
    wsBeräkning.Range("B5").Formula = "=B4/B2"
    wsBeräkning.Range("B5").NumberFormat = "0.0%"

    MsgBox "Beräkningar klara!", vbInformation
End Sub

Explanation

  • Filters data for the current month (based on today’s date)
  • Sums sales and costs
  • Calculates profit and margin
  • Writes everything to the Beräkningar sheet

In a real situation you would have more key figures, perhaps broken down by product, region, salesperson, etc. The principle is the same.

Step 3: Create a formatted report

Now we take the values from the Beräkningar sheet and create a neat report.

The code for report generation

Sub GenereraRapport()
    Dim wsBeräkning As Worksheet
    Dim wsRapport As Worksheet

    Set wsBeräkning = ThisWorkbook.Worksheets("Beräkningar")
    Set wsRapport = ThisWorkbook.Worksheets("Rapport")

    ' Rensa gammal rapport
    wsRapport.Cells.Clear

    ' Rubrik
    wsRapport.Range("A1").Value = "MÅNADSRAPPORT"
    wsRapport.Range("A1").Font.Size = 18
    wsRapport.Range("A1").Font.Bold = True

    wsRapport.Range("A2").Value = "Period: " & Format(Date, "mmmm yyyy")
    wsRapport.Range("A2").Font.Italic = True

    ' Nyckeltal
    wsRapport.Range("A4").Value = "RESULTATÖVERSIKT"
    wsRapport.Range("A4").Font.Bold = True
    wsRapport.Range("A4").Font.Size = 12

    ' Kopiera nyckeltal från Beräkningar
    wsBeräkning.Range("A2:B5").Copy wsRapport.Range("A6")

    ' Formatera tabellen
    With wsRapport.Range("A6:B9")
        .Borders.LineStyle = xlContinuous
        .Columns(2).HorizontalAlignment = xlRight
    End With

    ' Gör rubrikerna feta
    wsRapport.Range("A6:A9").Font.Bold = True

    ' Färgkoda resultatet
    If wsRapport.Range("B9").Value > 0 Then
        wsRapport.Range("B9").Interior.Color = RGB(198, 239, 206)  ' Grönt för positivt
    Else
        wsRapport.Range("B9").Interior.Color = RGB(255, 199, 206)  ' Rött för negativt
    End If

    ' Anpassa kolumnbredder
    wsRapport.Columns("A:B").AutoFit

    ' Lägg till genererad-tid
    wsRapport.Range("A15").Value = "Rapport genererad: " & Format(Now, "yyyy-mm-dd hh:mm")
    wsRapport.Range("A15").Font.Size = 8
    wsRapport.Range("A15").Font.Italic = True

    MsgBox "Rapport genererad!", vbInformation
End Sub

Result

You now get a neat report with: – A clear heading – The current period – Key figures in a formatted table – Colour coding of results – Timestamp

Step 4: Add a chart

A chart makes the report more visual and easier to take in.

The code for the chart

Sub SkapaGraf()
    Dim wsRapport As Worksheet
    Dim grafObjekt As ChartObject

    Set wsRapport = ThisWorkbook.Worksheets("Rapport")

    ' Ta bort eventuell tidigare graf
    Dim obj As ChartObject
    For Each obj In wsRapport.ChartObjects
        obj.Delete
    Next obj

    ' Skapa ny graf
    Set grafObjekt = wsRapport.ChartObjects.Add(Left:=300, Top:=100, Width:=400, Height:=250)

    With grafObjekt.Chart
        .ChartType = xlColumnClustered

        ' Data för grafen (Försäljning och Kostnader)
        .SetSourceData Source:=wsRapport.Range("A6:B8")

        .HasTitle = True
        .ChartTitle.Text = "Försäljning vs Kostnader"

        .HasLegend = False

        ' Formatera axlar
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Belopp (kr)"
    End With

    MsgBox "Graf skapad!", vbInformation
End Sub

Customisation

You can change the chart type: – xlColumnClustered – Column chart – xlLine – Line chart – xlPie – Pie chart – xlArea – Area chart

Step 5: Combine into one complete macro

Now we combine all the steps into a single main macro.

The main macro

Sub GenereraMånadsrapport()
    On Error GoTo FelHantering

    ' Stäng av skärmuppdatering för hastighet
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Kör alla steg i ordning
    Call ImporteraData
    Call BeräknaNyckeltal
    Call GenereraRapport
    Call SkapaGraf

    ' Spara rapporten med datumstämplat filnamn
    Dim rapportNamn As String
    rapportNamn = "Månadsrapport_" & Format(Date, "yyyy-mm-dd") & ".xlsx"

    ThisWorkbook.Worksheets("Rapport").Copy
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Genererade rapporter\" & rapportNamn
    ActiveWorkbook.Close SaveChanges:=False

    ' Slå på skärmuppdatering igen
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox "Komplett rapport genererad och sparad som:" & vbCrLf & rapportNamn, vbInformation

    Exit Sub

FelHantering:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Ett fel uppstod: " & Err.Description, vbCritical
End Sub

What happens here?

  1. Turns off screen updating for faster execution
  2. Runs all the sub-steps in order
  3. Copies the Rapport sheet to a new workbook
  4. Saves with a date-stamped file name
  5. Closes the new workbook
  6. Restores the Excel settings
  7. Error handling to catch any problems

Step 6: Make it user-friendly

Add a button so users can easily run the report.

Create a run button

Step 1: Go to the Rapport sheet

Step 2: Developer > Insert > Button (Form Control)

Step 3: Draw the button where you want it

Step 4: Select “GenereraMånadsrapport” in the dialog box

Step 5: Right-click the button > Edit Text

Step 6: Type “GENERATE REPORT”

Step 7: Format the button (right-click > Format Control for colour and size)

Now anyone can click the button and get a complete report in seconds.

Bonus feature: Send by email

If you want to send the report automatically by email, add this:

The code for sending email

Sub SkickaRapportMail()
    Dim OutlookApp As Object
    Dim Mail As Object
    Dim rapportFilNamn As String

    ' Senast genererade rapporten
    rapportFilNamn = ThisWorkbook.Path & "\Genererade rapporter\Månadsrapport_" & _
                     Format(Date, "yyyy-mm-dd") & ".xlsx"

    ' Kontrollera att filen finns
    If Dir(rapportFilNamn) = "" Then
        MsgBox "Ingen rapport hittades. Generera rapporten först.", vbExclamation
        Exit Sub
    End If

    ' Skapa mail
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mail = OutlookApp.CreateItem(0)

    With Mail
        .To = "chef@företag.se"
        .CC = "ekonomi@företag.se"
        .Subject = "Månadsrapport " & Format(Date, "mmmm yyyy")
        .Body = "Hej," & vbCrLf & vbCrLf & _
                "Bifogat finner ni månadsrapporten för " & Format(Date, "mmmm yyyy") & "." & vbCrLf & vbCrLf & _
                "Nyckeltal:" & vbCrLf & _
                "- Försäljning: " & Format(ThisWorkbook.Worksheets("Beräkningar").Range("B2").Value, "#,##0") & " kr" & vbCrLf & _
                "- Resultat: " & Format(ThisWorkbook.Worksheets("Beräkningar").Range("B4").Value, "#,##0") & " kr" & vbCrLf & vbCrLf & _
                "Mvh," & vbCrLf & "Automatiserad rapportering"

        .Attachments.Add rapportFilNamn

        .Display  ' Använd .Send för att skicka direkt utan att visa
    End With

    Set Mail = Nothing
    Set OutlookApp = Nothing
End Sub

Add a call to Call SkickaRapportMail in the main macro if you want to send automatically.

The time saved

Manual process: – Open source files: 5 min – Copy data: 10 min – Calculate key figures: 5 min – Format report: 15 min – Create chart: 5 min – Save and send: 5 min – Total: 45 minutes

Automated process: – Click the button: 10 seconds – Wait for the run: 30 seconds – Check the result: 2 minutes – Total: 3 minutes

Saving per month: 42 minutes Saving per year: 8.4 hours

And this is just ONE report. Most people have several.

Adapting it to your reports

The system is built modularly so you can easily adapt it:

More data sources: Add more import blocks in ImporteraData

More key figures: Extend BeräknaNyckeltal with your specific calculations

A different layout: Change GenereraRapport to your desired structure

Different charts: SkapaGraf can be adapted to any visualisation

Next steps

You now have a working reporting system. To take it further:

1. Add error handling What happens if a source file is missing? Add checks:

If Dir(källSökväg & "Försäljning.xlsx") = "" Then
    MsgBox "Källfilen Försäljning.xlsx saknas!", vbCritical
    Exit Sub
End If

2. Make it flexible for different periods Let the user choose the month instead of always using the current month.

3. Log report runs Write to a log every time a report is generated (who, when, result).

4. Add comparisons Compare against the previous month or the budget. Show variances.

5. Build a dashboard Instead of a simple report, build an interactive dashboard with buttons for different views.

Get professional help

If you want a robust reporting system but do not have time to build it yourself, we are happy to help at Excel Department.

We build tailor-made reporting systems that: – Handle complex data sources (databases, multiple systems, APIs) – Include advanced error handling and logging – Have user-friendly interfaces – Are documented and easy to maintain – Follow the company’s security requirements

Read more about how we helped a client save 20 hours a week with automated reporting.

Book a free consultation and we will discuss your reporting needs.

Or learn to build it yourself on our VBA course, where we go through reporting automation from the ground up.

Written by Lukas Lilja, Excel Department. We have built automated reporting systems for companies with 10 to 10,000 employees.

Do you need help with this? Our VBA course helps you – the first consultation is free of charge.

Book a free call or call 010-264 20 20.