基于单元格值重命名Excel工作表的VBA代码需求

Hey there, I've put together a VBA script tailored exactly to your needs—renaming the second and third worksheets using the value from cell A1 of your first (data source) sheet, then exporting both as separate PDF files. Here's the code:

Sub RenameSheetsAndExportPDF()
    Dim sourceSheet As Worksheet
    Dim targetSheetF As Worksheet
    Dim targetSheetE As Worksheet
    Dim baseName As String
    Dim saveDirectory As String
    
    ' Assign references to your worksheets (using their position in the workbook)
    Set sourceSheet = ThisWorkbook.Worksheets(1)
    Set targetSheetF = ThisWorkbook.Worksheets(2)
    Set targetSheetE = ThisWorkbook.Worksheets(3)
    
    ' Grab the base name from A1 of the data source sheet, trim extra spaces
    baseName = Trim(sourceSheet.Range("A1").Value)
    
    ' Validate that A1 isn't empty (prevents invalid worksheet/file names)
    If baseName = "" Then
        MsgBox "Oops! The A1 cell in your first worksheet is empty. Please add a value there first.", vbExclamation
        Exit Sub
    End If
    
    ' Rename the target worksheets with the required suffixes
    On Error Resume Next ' Handle cases where the generated name already exists
    targetSheetF.Name = baseName & "-F"
    targetSheetE.Name = baseName & "-E"
    On Error GoTo 0 ' Reset error handling
    
    ' Set the save location to the same folder as your Excel file
    saveDirectory = ThisWorkbook.Path & "\"
    
    ' Export each worksheet as a PDF (using their new names as the PDF file names)
    targetSheetF.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveDirectory & targetSheetF.Name & ".pdf", _
        Quality:=xlQualityStandard
    
    targetSheetE.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=saveDirectory & targetSheetE.Name & ".pdf", _
        Quality:=xlQualityStandard
    
    MsgBox "Done! Worksheets renamed and PDFs saved to your Excel file's folder.", vbInformation
End Sub

Key Details About the Script: #

• Worksheet References: The script uses worksheet indices (1, 2, 3) to target your sheets—this works as long as their order stays the same as you described.
• A1 Validation: It checks if cell A1 is empty and alerts you, since an empty base name would cause invalid worksheet or file names.
• Error Handling for Duplicate Names: The On Error Resume Next line prevents the script from crashing if a worksheet with the generated name (e.g., "Joy Nice-F") already exists in the workbook.
• PDF Save Location: PDFs are saved to the same folder where your Excel file is stored, using the newly renamed worksheet names as the PDF file names (so you'll get Joy Nice-F.pdf and Joy Nice-E.pdf for your example).

To use this:

1. Open your Excel file
2. Press Alt + F11 to open the VBA Editor
3. Insert a new module (Right-click your workbook in the Project Explorer > Insert > Module)
4. Paste the code above into the module
5. Run the macro (F5 in the editor, or via the Developer tab > Macros in Excel)

内容的提问来源于stack exchange，提问作者jack