Use an Excel Cell Value for a VBA PDF File Name and Email Subject
Question details
Use the value in cell A1 instead of the worksheet name for a PDF file name and email subject in an Excel VBA macro.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro exports a worksheet as a PDF and emails it.
- Observed behavior
- The macro currently uses the worksheet name for the PDF file name and email subject.
Check that A1 on the worksheet to be exported contains the text you want to use and that it is valid as a file name. Open the workbook containing the existing PDF export and email macro.
Replace the worksheet name with the value in cell A1
Use the worksheet object's Cells property for both the PDF path and the email subject.
The Text property returns the displayed cell text. Make sure A1 is not blank and does not contain file-name characters such as /, \, :, *, ?, ", <, >, or |.
In Excel, press Alt+F11 to open the VBA editor, then open the module containing the PDF export and email macro.
Before the macro reads A1, make sure xSht refers to the intended worksheet. For example, use `Set xSht = ThisWorkbook.Worksheets("Sheet1")` and replace Sheet1 with the actual sheet name.
Find the line that appends `xSht.Name` to xFolder and replace it with `xFolder = xFolder & "\" & xSht.Cells(1, 1).Text & ".pdf"`. This assumes xFolder contains the destination folder without a trailing backslash.
Inside the existing email object's With block, replace the worksheet-name subject with `.Subject = xSht.Cells(1, 1).Text & ".pdf"`. Remove `& ".pdf"` if you do not want the extension in the subject.
Run the existing macro from Excel's Developer > Macros menu, then check the exported PDF name and the email subject.

Edit and Export Spreadsheets with WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for opening and editing spreadsheets and exporting them to PDF. It supports common Excel formats for an easier move between applications, but it does not fix errors in Microsoft Excel VBA macros.

Frequently Asked Questions
Why is xCells highlighted as an undefined function?
The cell reference should use the worksheet object and its Cells property: `xSht.Cells(1, 1).Text`. Do not substitute `xCells` or declare it as CellFormat.
How do I fix run-time error 91 on the cell reference?
Assign the worksheet before accessing its cells, for example with `Set xSht = ThisWorkbook.Worksheets("Sheet1")`. Use the actual worksheet name and ensure the assignment runs before the reference.
Can the email subject omit the PDF extension?
Yes. Inside the email object's With block, use `.Subject = xSht.Cells(1, 1).Text` without appending `& ".pdf"`.
Why might the PDF export fail even after changing the code?
Check that A1 is not blank and does not contain characters prohibited in file names, such as a slash or colon. Also verify that xFolder points to an existing destination folder.




