How to Send a Separate Report to Each Client

One master dataset, thirty recipients, and each one may only see their own rows. Here is how to split it, route it, and not send Client A's numbers to Client B.

Quick version: split the master with Show Report Filter Pages, keep a client-to-email mapping table, and loop the mapping table in Power Automate to build and send one file each. The failure mode to design against is not effort — it is sending the wrong slice to the wrong person.

1. Split the master with a PivotTable

Almost nobody knows this command exists. Build a PivotTable over your data, drag the client (or manager, or region) field into the Filters area, then go to PivotTable Analyze → Options ▾ → Show Report Filter Pages. Pick the field and click OK.

Excel creates one worksheet per client, each pre-filtered to that client and named after them. Thirty clients, two clicks. If you have been doing this by copying and filtering by hand, this is the single biggest time saving on the page.

When each client needs raw rows rather than a summary, use Power Query instead: filter on a parameter tied to a named cell, and change the cell to re-point the query.

2. Export one file per sheet

PDF is usually the right format to send — it cannot be re-sorted into someone else's numbers by accident and it renders on a phone. A short macro handles the loop:

Sub ExportSheetsAsPDF()
    Dim ws As Worksheet, folder As String
    folder = ThisWorkbook.Path & "\reports\"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Data" And ws.Name <> "Recipients" Then
            ws.ExportAsFixedFormat xlTypePDF, folder & ws.Name & ".pdf"
        End If
    Next ws
End Sub

Note the exclusion list. Forgetting it is how the source data sheet ends up in an attachment.

3. The recipient mapping table

This is the part that makes the whole thing maintainable. One Excel table, two columns — ClientKey and Email — and nothing else defines who receives what. When a contact changes you edit one cell, not a flow. Before any real send you can read the table top to bottom and see exactly where thirty files are going.

Add a third column, Active, so you can pause a recipient without deleting the row.

4. Loop it in Power Automate

Scheduled cloud flow, then:

1. Recurrence — weekly or monthly, timezone set explicitly.
2. Excel Online (Business) → List rows present in a table, pointed at the mapping table. Add a filter query so only Active rows come back.
3. Apply to each over the returned rows. Inside the loop: run an Office Script that takes the client key as a parameter and returns that client's rows, then Create CSV table to turn them into an attachment body.
4. Send an email (V2) — To is the row's Email, attachment name includes the client key so a mis-send is visible in the subject line, not buried in the file.

Put the client name in the subject too. It is the cheapest possible safeguard: the recipient spots a wrong-name email instantly, and so do you in your sent items.

The two failure modes worth designing around

Stale copies. The moment a file lands in an inbox it is a snapshot. Restate a figure a week later and there are now two versions of the truth in circulation, and the recipient is holding the older one. This is not a small problem in client work — it is most of the "your numbers don't match ours" conversations.

Cross-contamination. Hidden rows and hidden sheets travel with a workbook. Anyone who unhides them sees everything. If you send Excel files rather than PDFs, build each one from a filtered copy, never from the master with rows hidden.

The other shape of this problem

Everything above ships N files. The alternative is to ship none: keep one dataset and give each recipient access to their own view of it, so nothing goes stale and there is no loop to get wrong.

That is how Quiriz handles it — access is governed at the dataset and project level, so the usual pattern is one project per client with the relevant data and reports inside it. Each client reads a live report rather than an attachment from last Tuesday, and can ask their own follow-up questions against their own data without waiting for you to re-run anything.

Straight about the limits: this is project- and dataset-level access, not row-level filtering inside a shared file. If your data genuinely has to live in one table with per-row permissions, the split-and-send loop above is still the right build.

Frequently asked questions