PDF is saved twice rather than once

Question

I want to save my xls document as a PDF.
Some documents include the "Readme" sheet, some don't.
In this event, I set the condition leading to mandatory saving despite of the "Readme" sheet presence.

I found some hint here: Test or check if sheet exists

Sub sheet_exist()
 Dim I As Long
 For I = 1 To Worksheets.Count
  If Worksheets(I).Name = "Readme" Then
    Sheets("Readme").Visible = False

  ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True

  Sheets("Readme").Visible = True
  Else
  ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
   End If
  Next I
 End Sub

My PDF document is saved twice. How can I eliminate the second save?

Michael Wycisk · Accepted Answer · 2020-07-30T11:42:18.997

Your for/next loop is currently structured in a way that it will save your sheet with every iteration (it will first check if the current sheet is "Readme" and then save the the workbook - for every sheet in the workbook). So if you have two sheets in your workbook, it will save your workbook twice.

You need to restructure your code, so it first checks for the existence of "Readme" and then saves the workbook once.

Sub sheet_exist()

    Dim I As Long
    Dim bReadmeExists As Boolean
    
    'First, we loop through all sheets and check if "Readme" exists. If it does, we hide the sheet.
    For I = 1 To Worksheets.Count
        If Worksheets(I).Name = "Readme" Then
            Sheets("Readme").Visible = False
            'If readme exists, we need to make it visible again in the end
            bReadmeExists = True
        End If
    Next I
            
    'Now we export the workbook once
    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                     ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
                                     Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                     IgnorePrintAreas:=False, OpenAfterPublish:=True
    
    'Finally, we make the readme sheet visible again
    If bReadmeExists Then
        Sheets("Readme").Visible = True
    End If
    
End Sub

I have also added a boolean variable to "remember" if the readme sheets exists (so we eventually can make it visible again after the export). I also allowed my self to indent the code correctly, so it is easier to read.

PDF is saved twice rather than once

1 Answers1