How can I remove

I tried to create a PDF file using following script, it works but when I remove ws.Name, the script breaks.

Sub createPDFfiles()
    Dim ws As Worksheet
    Dim strName As String
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next 'Continue if an error occurs

        ' Name PDF files based on the worksheet Index (e.g Annex 1.1.1, Annex 1.1.2, etc.)
        strName = Range("A10").Text & " " & Range("C7").Text & ws.Name

        ' If you want to name the PDF files differently just change the Fname variable above to
        ' whatever you like. For example if you changed Fname to:
        '  Fname =  "C:\myFolder\pdfs\" & ActiveWorkbook.Name & "-" & ws.Name
        '  The files would be stored in C:\myFolder\pdfs, and named using the
        ' spreadsheet file name and the worksheet name.
        ' WARNING: Using worksheet names may cause errors if the  names contain characters that Windows
        ' does not accept in file names. See below for a list of characters that you need to avoid.
        ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
    Next ws
End Sub

How can I remove this ws.Name and still work?

1 answer

  • answered 2018-10-16 07:28 Olly

    Your Range objects are unqualified in this statement:

     strName = Range("A10").Text & " " & Range("C7").Text & ws.Name

    So they are referring to those cells on the active sheet, rather than on sheet ws.

    If you wanted to refer to those cells on each worksheet you loop through, then qualify them appropriately:

     strName = ws.Range("A10").Text & " " & ws.Range("C7").Text

    You may wish to test that strName is not null before you try to save the PDF.

    I'd also recommend losing the On Error Resume Next line, and handling errors properly.