Existing Macro throwing error

D12345678910M 

The error in your macro might be due to several potential issues. Let's troubleshoot and correct your macro step by step. The highlighted area suggests that there might be an issue with the Range("P10") reference or with the ExportAsFixedFormat method.

Revised Macro Code

Here is a revised version of your macro with some added error handling and improved variable usage:

Vba Code is untested backup your file.

Sub GeneratePDF4email()

    Dim end_date As String
    Dim path As String
    Dim fname As String
    Dim ws As Worksheet
    Dim member As String

    ' Set the path where the PDFs will be saved
    path = "J:\SFSSRS\GL\E-Commerce Analyst\Micro Fulfillment Centers (MFC)\MFC Weekly Flash\Attachments\"

    ' Loop through each worksheet in the workbook
    For Each ws In Worksheets
        ' Get the name of the current worksheet
        member = ws.Name

        ' Get the end_date value from cell P10 of the current worksheet
        ' Ensure you reference the correct worksheet for the end_date value
        On Error Resume Next
        end_date = ws.Range("P10").Value
        On Error GoTo 0

        ' Set the file name for the PDF
        fname = member

        ' Export the worksheet to a PDF file if it is not the "QTD Comparison" sheet
        If ws.Name <> "QTD Comparison" Then
            On Error Resume Next
            ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & fname & ".pdf", IgnorePrintAreas:=False
            If Err.Number <> 0 Then
                MsgBox "Error exporting sheet: " & ws.Name & " to PDF. Error: " & Err.Description
                Err.Clear
            End If
            On Error GoTo 0
        End If
    Next ws

End Sub

Explanation of Changes and Additions

1. Error Handling: Added error handling using On Error Resume Next and On Error GoTo 0 to catch and display any errors that occur during the export process.
2. File Extension: Added ".pdf" to the file name to ensure that the saved file has the correct extension.
3. Reference Worksheet for Range: Ensured that the Range("P10") reference is specific to the current worksheet in the loop.
4. Debugging Message: Added a MsgBox to display an error message if the ExportAsFixedFormat method fails.

Possible Issues and Solutions

1. Path Existence: Ensure that the path "J:\SFSSRS\GL\E-Commerce Analyst\Micro Fulfillment Centers (MFC)\MFC Weekly Flash\Attachments\" exists and is accessible.
2. Permissions: Verify that you have write permissions to the specified path.
3. Cell Reference: Ensure that cell P10 exists and contains a valid value on each worksheet.
4. Worksheet Names: Ensure that there are no special characters in worksheet names that could cause issues when used as file names.

By running the revised macro, you should get better insights into any issues that occur, and it should handle potential errors more gracefully. If you continue to experience issues, please provide details about any specific error messages you receive. The text, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.