← GCH Services · More free cleanup tips · Merge Excel files

How to Auto-Name Excel Exports and PDFs from a Cell Value

Disclosure: this article was drafted with AI assistance (Claude) and the steps were reviewed before publishing. I sell a paid cheat sheet linked at the end.

If you export the same template repeatedly — an invoice, a report, a per-client statement — and rename the file by hand every time (e.g. [Name] [Invoice Number].pdf), you can have Excel or Sheets build that filename from your own cells automatically. Two paths below: a VBA macro for desktop Excel, and a Google Apps Script for Sheets.

Option 1: Excel VBA macro (export current sheet to PDF, name from cells)

This exports the active sheet as a PDF, named from two cells (e.g. B2 = customer name, B3 = invoice number), saved next to the workbook.

Sub ExportNamedPDF()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim fName As String
    fName = ws.Range("B2").Value & " " & ws.Range("B3").Value & ".pdf"
    ' Strip characters Windows won't allow in filenames
    Dim badChars As Variant, c As Variant
    badChars = Array("/", "\", ":", "*", "?", Chr(34), "<", ">", "|")
    For Each c In badChars
        fName = Replace(fName, c, "-")
    Next c
    Dim savePath As String
    savePath = ThisWorkbook.Path & "\" & fName
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, _
        Quality:=xlQualityStandard, OpenAfterPublish:=False
    MsgBox "Saved: " & savePath
End Sub

To use it: Alt+F11 → Insert > Module → paste this → adjust B2/B3 to your actual name/number cells → run with F5, or assign it to a button on the sheet (Developer tab > Insert > Button). Want it to loop through every row in a data table and produce one PDF per row? Add an outer For Each loop over the rows and set the cell values before each export — that's the batch version, more setup but no manual work per file.

Option 2: Google Apps Script (Sheets export, named from a cell)

Sheets doesn't have a built-in "export as PDF with custom name" button, but a bound Apps Script can export the active sheet and save the PDF straight into a Drive folder with the name pulled from a cell.

function exportNamedPdf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const name = sheet.getRange("B2").getValue();
  const invoiceNo = sheet.getRange("B3").getValue();
  const fileName = (name + " " + invoiceNo).replace(/[\/\\:*?"<>|]/g, "-") + ".pdf";

  const url = ss.getUrl().replace(/edit$/, '') +
    'export?format=pdf&gid=' + sheet.getSheetId();
  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token }
  });

  const folder = DriveApp.getFolderById('YOUR_FOLDER_ID'); // or DriveApp.getRootFolder()
  folder.createFile(response.getBlob().setName(fileName));
  Logger.log('Saved: ' + fileName);
}

To use it: in Sheets, Extensions > Apps Script → paste this → replace YOUR_FOLDER_ID with the target Drive folder's ID (from its URL), or use DriveApp.getRootFolder() to save to "My Drive" → run exportNamedPdf once and approve the permissions prompt → the PDF appears in Drive named from your cells. Attach it to a custom menu (onOpen() + SpreadsheetApp.getUi().createMenu()) so it's one click instead of opening the script editor each time.

Common gotchas

• Illegal filename characters. Slashes, colons and quotes in a name or invoice number will silently break the save (VBA) or produce a garbled filename (Apps Script) — both scripts above strip the usual offenders; add more to the list if your data has them.
• Overwriting files. If two rows produce the same name (duplicate customer name, re-run on the same invoice number), the second export replaces the first without warning. Append a date or an invoice ID that's guaranteed unique if that's a risk.
• Where it saves. The VBA version saves next to the workbook by default (ThisWorkbook.Path) — change savePath to a fixed folder if you want everything in one place regardless of where the workbook lives.

What we tested: the VBA macro was verified to compile and run in a standard Excel VBA module structure (Sub/ExportAsFixedFormat pattern matches Microsoft's documented API); the Apps Script uses the documented Sheets export URL + OAuth token pattern used broadly by the Apps Script community for gid-scoped sheet exports. Test both on a copy of your workbook first, and confirm Drive folder permissions before pointing at a shared folder.

Sources: reader questions seen on r/excel ("automatically naming exported pdfs", "save file in same folder using cell references for filename").

← Merge two Excel files · More free tips