VBA excel: export all type of sheet into pdf

Isn't the issue that it is a Chart and not a Sheet, so you'd need to use Chart.ExportAsFixedFormat ?

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.chart.exportasfixedformat?view=vsto-2017

Something more along the lines of

Sub Pdf_ActiveSheet()
10        On Error GoTo Error_Handler
          Dim wsA                   As Excel.Chart 'Worksheet
          Dim wbA                   As Excel.Workbook
          Dim strTime               As String
          Dim strName               As String
          Dim strPath               As String
          Dim strFile               As String
          Dim strPathFile           As String
          Dim myFile                As Variant

30        Set wbA = ActiveWorkbook
40        Set wsA = ActiveChart 'ActiveSheet
50        strTime = Format(Now(), "yyyymmdd\_hhmm")

          'get active workbook folder, if saved
60        strPath = wbA.Path
70        If strPath = "" Then
80            strPath = Application.DefaultFilePath
90        End If
100       strPath = strPath & "\"

          'replace spaces and periods in sheet name
110       strName = Replace(wsA.Name, " ", "")
120       strName = Replace(strName, ".", "_")

          'create default name for savng file
130       strFile = strName & "_" & strTime & ".pdf"
140       strPathFile = strPath & strFile

          'use can enter name and
          ' select folder for file
150       myFile = Application.GetSaveAsFilename _
                   (InitialFileName:=strPathFile, _
                    FileFilter:="PDF Files (*.pdf), *.pdf", _
                    Title:="Select Folder and FileName to save")

          'export to PDF if a folder was selected
160       If myFile <> "False" Then
170           wsA.ExportAsFixedFormat _
                      Type:=xlTypePDF, _
                      Filename:=myFile, _
                      Quality:=xlQualityStandard, _
                      IncludeDocProperties:=True, _
                      IgnorePrintAreas:=False, _
                      OpenAfterPublish:=False
              'confirmation message with file info
180           MsgBox "PDF file has been created: " _
                     & vbCrLf _
                     & myFile
190       End If

Error_Handler_Exit:
200       On Error Resume Next
          If Not wsA Is Nothing Then Set wsA = Nothing
          If Not wbA Is Nothing Then Set wbA = Nothing
210       Exit Sub

Error_Handler:
220       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: Pdf_ActiveSheet" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
230       Resume Error_Handler_Exit
End Sub

Select allOpen in new window

The issue is, ActiveSheet can be either a Chart Sheet or a Worksheet and both are different sheet objects.
The solution is, declare wsA as object so that it can hold both the Chart Sheet and the Worksheet as well.

Sub Pdf_ActiveSheet()
Dim wsA As Object
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

Select allOpen in new window

Nice solution to make the code adaptive.

Thanks Daniel!

Thank you very much for this proposal. Unfortunately I test the procedure with two charts and it exports just one. Do you know how can we manage this case related to one sheets containing multiple  charts?

Unfortunately I test the procedure with two charts and it exports just one.

By two charts, do you mean two charts on the same sheet or two sheets with one chart each?

The existing procedure will export the ActiveSheet as pdf file not all the sheets? What's your requirement exactly?

Also, you will need to change the page layout of the sheet depending on the data/chart present on the sheet so that the data/chart appears correctly on the pdf file.

Hello Subodh,

1-Two charts in one sheet, The procedure should exports just active sheet.
I just defined the layout of my sheet and both charts are properly exported. Sorry for that. Do you think that it is straightforward to finish the procedure by opening folder location in which pdf file has been generated?
Thank you in advance for your help.

You are right, thank you very much for your help. however the idea is to open windows folder in order to get quickly the file location and send it if necessary. Do you think it we can add the condition to open folder if the file has been properly exported?

To open the folder where the pdf file was saved successfully, just add the following line after the line#50.

Shell "C:\WINDOWS\explorer.exe """ & strPath & "", vbNormalFocus

Select allOpen in new window

Noted, Thank you very much for your help!!!

You're welcome!