Excel Macro: Auto-Generate Avails Reports from Your Rights Grid

Every buyer wants avails in a different format. A French distributor wants to see everything available in France, grouped by window type. A US platform wants all SVOD avails across every title, sorted by genre. An Asian aggregator wants separate PDFs for Japan, Korea, and India. You end up spending hours copying rows, reformatting headers, adjusting column widths, and exporting PDFs one at a time.

This tutorial gives you a VBA macro that reads your rights grid and generates polished, territory-grouped avails reports with a single click. It also includes a PDF export function that creates one file per territory, ready to attach to an email or upload to a buyer portal.

Step 1: Prepare Your Rights Grid

The macro expects a sheet called AvailsGrid with the following columns starting in row 1:

Column	Header	Format
A	Title	Text
B	Territory	ISO 3166-1 alpha-2 code (e.g., US, GB, FR)
C	Window Type	SVOD, TVOD, EST, Free TV, Pay TV, Theatrical
D	Start Date	Date (MM/DD/YYYY or your locale’s format)
E	End Date	Date
F	Exclusivity	Exclusive or Non-Exclusive
G	Status	Available, Licensed, Holdback, Expired

Only rows with a Status of “Available” will appear in the generated reports. Licensed, Holdback, and Expired entries are excluded because buyers only need to see what they can actually acquire.

Step 2: The Report Generation Macro

Open the VBA editor (Alt+F11), insert a new module, and paste the following code. This macro reads every “Available” row from the AvailsGrid, groups them by territory, and writes them to a new sheet called AvailsReport.

Sub GenerateAvailsReport()
    Dim srcWs As Worksheet
    Dim rptWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim rptRow As Long
    Dim territories As Object
    Dim territory As Variant
    Dim key As Variant

    Set srcWs = ThisWorkbook.Sheets("AvailsGrid")
    lastRow = srcWs.Cells(srcWs.Rows.Count, "A").End(xlUp).Row

    ' Delete existing report sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("AvailsReport").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' Create new report sheet
    Set rptWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    rptWs.Name = "AvailsReport"

    ' Collect unique territories in order of appearance
    Set territories = CreateObject("Scripting.Dictionary")
    For i = 2 To lastRow
        If UCase(Trim(srcWs.Cells(i, 7).Value)) = "AVAILABLE" Then
            Dim terr As String
            terr = UCase(Trim(srcWs.Cells(i, 2).Value))
            If Not territories.Exists(terr) Then
                territories.Add terr, terr
            End If
        End If
    Next i

    ' Build the report grouped by territory
    rptRow = 1

    ' Report title
    rptWs.Cells(rptRow, 1).Value = "Avails Report - Generated " & Format(Date, "MMMM D, YYYY")
    rptWs.Cells(rptRow, 1).Font.Size = 14
    rptWs.Cells(rptRow, 1).Font.Bold = True
    rptWs.Range(rptWs.Cells(rptRow, 1), rptWs.Cells(rptRow, 6)).Merge
    rptRow = rptRow + 2

    For Each key In territories.Keys
        ' Territory header
        rptWs.Cells(rptRow, 1).Value = "Territory: " & key
        rptWs.Cells(rptRow, 1).Font.Size = 12
        rptWs.Cells(rptRow, 1).Font.Bold = True
        rptWs.Cells(rptRow, 1).Interior.Color = RGB(50, 50, 80)
        rptWs.Cells(rptRow, 1).Font.Color = RGB(255, 255, 255)
        rptWs.Range(rptWs.Cells(rptRow, 1), rptWs.Cells(rptRow, 5)).Interior.Color = RGB(50, 50, 80)
        rptWs.Range(rptWs.Cells(rptRow, 1), rptWs.Cells(rptRow, 5)).Font.Color = RGB(255, 255, 255)
        rptRow = rptRow + 1

        ' Column headers for this territory section
        rptWs.Cells(rptRow, 1).Value = "Title"
        rptWs.Cells(rptRow, 2).Value = "Window"
        rptWs.Cells(rptRow, 3).Value = "Start Date"
        rptWs.Cells(rptRow, 4).Value = "End Date"
        rptWs.Cells(rptRow, 5).Value = "Exclusivity"
        rptWs.Range(rptWs.Cells(rptRow, 1), rptWs.Cells(rptRow, 5)).Font.Bold = True
        rptWs.Range(rptWs.Cells(rptRow, 1), rptWs.Cells(rptRow, 5)).Interior.Color = RGB(220, 220, 235)
        rptRow = rptRow + 1

        ' Data rows for this territory
        Dim rowInSection As Long
        rowInSection = 0
        For i = 2 To lastRow
            If UCase(Trim(srcWs.Cells(i, 2).Value)) = key And _
               UCase(Trim(srcWs.Cells(i, 7).Value)) = "AVAILABLE" Then

                rptWs.Cells(rptRow, 1).Value = srcWs.Cells(i, 1).Value
                rptWs.Cells(rptRow, 2).Value = srcWs.Cells(i, 3).Value
                rptWs.Cells(rptRow, 3).Value = srcWs.Cells(i, 4).Value
                rptWs.Cells(rptRow, 4).Value = srcWs.Cells(i, 5).Value
                rptWs.Cells(rptRow, 5).Value = srcWs.Cells(i, 6).Value

                ' Format dates
                rptWs.Cells(rptRow, 3).NumberFormat = "MMM D, YYYY"
                rptWs.Cells(rptRow, 4).NumberFormat = "MMM D, YYYY"

                ' Alternating row color
                If rowInSection Mod 2 = 1 Then
                    rptWs.Range(rptWs.Cells(rptRow, 1), rptWs.Cells(rptRow, 5)).Interior.Color = RGB(245, 245, 250)
                End If

                rowInSection = rowInSection + 1
                rptRow = rptRow + 1
            End If
        Next i

        ' Add borders to this territory section
        Dim sectionStart As Long
        sectionStart = rptRow - rowInSection - 1 ' Includes column header row
        If rowInSection > 0 Then
            Dim borderRange As Range
            Set borderRange = rptWs.Range(rptWs.Cells(sectionStart, 1), rptWs.Cells(rptRow - 1, 5))
            borderRange.Borders(xlEdgeLeft).LineStyle = xlContinuous
            borderRange.Borders(xlEdgeRight).LineStyle = xlContinuous
            borderRange.Borders(xlEdgeTop).LineStyle = xlContinuous
            borderRange.Borders(xlEdgeBottom).LineStyle = xlContinuous
            borderRange.Borders(xlInsideHorizontal).LineStyle = xlContinuous
            borderRange.Borders(xlInsideVertical).LineStyle = xlContinuous
            borderRange.Borders(xlInsideHorizontal).Color = RGB(200, 200, 200)
            borderRange.Borders(xlInsideVertical).Color = RGB(200, 200, 200)
        End If

        rptRow = rptRow + 1 ' Blank row between territory sections
    Next key

    ' Auto-fit column widths
    rptWs.Columns("A:E").AutoFit

    ' Set minimum column widths for readability
    If rptWs.Columns("A").ColumnWidth < 30 Then rptWs.Columns("A").ColumnWidth = 30
    If rptWs.Columns("B").ColumnWidth < 15 Then rptWs.Columns("B").ColumnWidth = 15

    rptWs.Activate
    MsgBox territories.Count & " territory section(s) generated in the AvailsReport sheet.", vbInformation
End Sub

Step 3: Understanding the Formatting

The macro applies professional formatting automatically so the report is ready to share without manual touch-up:

Territory headers get a dark background with white text, making each section easy to find when scrolling through a long report.
Column sub-headers (Title, Window, Start Date, End Date, Exclusivity) are bold with a light background, providing clear labels for each section.
Alternating row colors on data rows improve readability, especially when a territory has dozens of available titles.
Borders are applied around each territory section. Inner borders use a light gray to keep the look clean rather than heavy.
Date formatting is normalized to “MMM D, YYYY” (e.g., “Apr 12, 2026”) regardless of what format the source data uses.
Column widths auto-fit to content, with minimum widths enforced on the Title and Window columns so they never appear cramped.

Step 4: Export Each Territory as a Separate PDF

This macro reads the AvailsReport sheet and exports each territory section to its own PDF file. The PDFs are saved to a folder you specify.

Sub ExportTerritoryPDFs()
    Dim rptWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim exportPath As String
    Dim sectionStart As Long
    Dim currentTerritory As String
    Dim territoryCount As Long

    Set rptWs = ThisWorkbook.Sheets("AvailsReport")
    lastRow = rptWs.Cells(rptWs.Rows.Count, "A").End(xlUp).Row

    ' Let user choose export folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder for PDF export"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Export cancelled.", vbInformation
            Exit Sub
        End If
        exportPath = .SelectedItems(1)
    End With

    If Right(exportPath, 1) <> "\" Then exportPath = exportPath & "\"

    territoryCount = 0
    sectionStart = 0

    For i = 1 To lastRow
        Dim cellVal As String
        cellVal = rptWs.Cells(i, 1).Value

        ' Detect territory header rows (they start with "Territory: ")
        If Left(cellVal, 11) = "Territory: " Then
            ' Export the previous section if there was one
            If sectionStart > 0 And Len(currentTerritory) > 0 Then
                Call ExportSection(rptWs, sectionStart, i - 2, exportPath, currentTerritory)
                territoryCount = territoryCount + 1
            End If
            currentTerritory = Mid(cellVal, 12)
            sectionStart = i
        End If
    Next i

    ' Export the last section
    If sectionStart > 0 And Len(currentTerritory) > 0 Then
        Call ExportSection(rptWs, sectionStart, lastRow, exportPath, currentTerritory)
        territoryCount = territoryCount + 1
    End If

    MsgBox territoryCount & " PDF(s) exported to " & exportPath, vbInformation
End Sub

Private Sub ExportSection(ws As Worksheet, startRow As Long, endRow As Long, path As String, territory As String)
    Dim printRange As Range
    Set printRange = ws.Range(ws.Cells(startRow, 1), ws.Cells(endRow, 5))

    ' Set print area to just this section
    ws.PageSetup.PrintArea = printRange.Address
    ws.PageSetup.Orientation = xlLandscape
    ws.PageSetup.FitToPagesWide = 1
    ws.PageSetup.FitToPagesTall = False
    ws.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
    ws.PageSetup.RightMargin = Application.InchesToPoints(0.5)
    ws.PageSetup.TopMargin = Application.InchesToPoints(0.5)
    ws.PageSetup.BottomMargin = Application.InchesToPoints(0.5)

    Dim fileName As String
    fileName = path & "Avails_" & territory & "_" & Format(Date, "YYYY-MM-DD") & ".pdf"

    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=fileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End Sub

After running ExportTerritoryPDFs, you will have files like Avails_US_2026-04-16.pdf, Avails_FR_2026-04-16.pdf, and Avails_JP_2026-04-16.pdf in your chosen folder, each containing only the avails relevant to that territory.

One-Click Report Generation

To make this a one-click workflow, add a button to your AvailsGrid sheet:

Go to the AvailsGrid sheet
Click Insert > Shapes and draw a rectangle
Type “Generate Report” on the shape
Right-click the shape, select Assign Macro, and choose GenerateAvailsReport
Create a second button labeled “Export PDFs” and assign it to ExportTerritoryPDFs

You can also create a combined macro that runs both steps in sequence:

Sub GenerateAndExport()
    Call GenerateAvailsReport
    Call ExportTerritoryPDFs
End Sub

This takes you from raw rights grid to territory-specific PDF reports in a single click. The report sheet stays in your workbook as a living document you can review before exporting, and the PDFs are date-stamped so you always know which version was sent to which buyer.