Save As .pdf VBA Returns Error on Hidden Sheets

[davidbrent]

Hello All,

I have a code that works great at saving open worksheets as separate .pdf but when i hide i sheet it returns an invalid call. I'm sure its a very simple fix but well beyond my beginner level unfortunately

Sub ExportAsPDF()

Dim Folder_Path As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Folder path"
If .Show = -1 Then Folder_Path = .SelectedItems(1)
End With

If Folder_Path = "" Then Exit Sub

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
sh.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & ".pdf"
Next

End Sub

 

[HaHoBe]

Hi davidbrent,

check if the worksheet is visible?

Sub ExportAsPDF()

Dim strFoldPath As String
Dim wksSheet As Worksheet

With Application.FileDialog(msoFileDialogFolderPicker)
  .Title = "Select Folder path"
  If .Show <> -1 Then Exit Sub
  strFoldPath = .SelectedItems(1)
End With

For Each wksSheet In ActiveWorkbook.Worksheets
  If wksSheet.Visible = xlSheetVisible Then
    wksSheet.ExportAsFixedFormat xlTypePDF, strFoldPath & Application.PathSeparator & wksSheet.Name & ".pdf"
  End If
Next wksSheet

End Sub

Ciao,
Holger

 

[davidbrent]

Hi davidbrent,

check if the worksheet is visible?

Sub ExportAsPDF()

Dim strFoldPath As String
Dim wksSheet As Worksheet

With Application.FileDialog(msoFileDialogFolderPicker)
  .Title = "Select Folder path"
  If .Show <> -1 Then Exit Sub
  strFoldPath = .SelectedItems(1)
End With

For Each wksSheet In ActiveWorkbook.Worksheets
  If wksSheet.Visible = xlSheetVisible Then
    wksSheet.ExportAsFixedFormat xlTypePDF, strFoldPath & Application.PathSeparator & wksSheet.Name & ".pdf"
  End If
Next wksSheet

End Sub

Ciao,
Holger

Thank you so much for this!