Exporting Some Sheets from Excel Workbook to PDF

Question

I am working on writing a VBA code to export some of the sheets in excel to same PDF. I have several chart sheets in my excel file each of which name ends with "(name)_Chart". I want to export all sheets with names ending wioth chart to one PDF file. Here is the code I am trying to write.

Sub FindWS()
        'look if it at least contains part of the name
        Dim s As Worksheet
        Dim strPath As String

        strPath = ActiveWorkbook.Path & "\"

        For Each s In ThisWorkbook.Sheets
            If InStr(1, s.Name, Chart) Then
                s.Activate
                ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & s.Name & ".pdf"
                Exit Sub
            End If
        Next s
End Sub

This code is not limting export to only the chart sheets but exporting thy whole workbook. Can anyone help me with figurint out whats is missing in my code.

Thanks!

MODIFIED CODE:

Sub FindWS()
'look if it at least contains part of the name
Dim s As Worksheet
Dim strPath As String

strPath = ActiveWorkbook.Path & "\"

For Each s In ThisWorkbook.Worksheets
      If InStr(1, s.Name, "Chart") = 0 Then
          ' Hide the sheet so it is not exported as PDF
          s.Visible = False
             End If
Next s
          With ActiveWorkbook
          .ExportAsFixedFormat xlTypePDF, strPath & "TEST.pdf"
                End With

End Sub

Solution

I am surprised that your code is running in the first place :) You should have actually got an error run time error '13', type mismatch

Sheets and Worksheets are two different things in Excel

The Worksheets collection is a collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet. Whereas the Sheets collection, on the other hand, consist of not only a collection of worksheets but also other types of sheets to include Chart sheets, Excel 4.0 macro sheets and Excel 5.0 dialog sheets.

So if you declare your object as Worksheet

Dim s As Worksheet

Then ensure that while looping you loop through the correct collection

For Each s In ThisWorkbook.Worksheets

and not

For Each s In ThisWorkbook.Sheets

else you will get a run time error '13', type mismatch

FOLLOWUP (Based on Comments)

@ Siddharth: 1. Yes, I want to export Chart sheets that ends with name "Chart". 2. I want all those charts in one PDF and the name of the PDF should be the "original" file name. (I will have to save the final PDF files in different location so there will be no overlapping of files.) – datacentric

Option Explicit

Sub Sample()
    Dim ws As Object
    Dim strPath As String, OriginalName As String, Filename As String

    On Error GoTo Whoa

    '~~> Get activeworkbook path
    strPath = ActiveWorkbook.Path & "\"
    '~~> Get just the name without extension and path
    OriginalName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
    '~~> PDF File name
    Filename = strPath & OriginalName & ".pdf"

    '~~> Loop through Sheets Collesction
    For Each ws In ActiveWorkbook.Sheets
        '~~> Check if it is a Chart Sheet and also it ends in "Chart"
        If ws.Type = 3 And UCase(Right(Trim(ws.Name), 5)) = "CHART" Then
            ws.Visible = True
        Else
            ws.Visible = False
        End If
    Next ws

    '~~> Export to pdf
    ActiveWorkbook.ExportAsFixedFormat xlTypePDF, Filename

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Comments

[Nick.McDermaid]

OH! I didn't realise your code didn't actually work before - let me fix it.

[datacentric]

Thanks for your response. I figured out that there is some issue with using sheets instead of worksheet. So, I modified my code but now its only exporting the first sheet in excel and not all sheets. The modified code is in my question above.

[Siddharth Rout]

If you want to check if the sheet is ending with "Chart" then instr() is not the right way. You have to use Right()

[datacentric]

Also, I have both worksheets and chart sheets in excel. I only want to export chart sheets.

[Siddharth Rout]

Ok before I give you the code, can you please confirm 2 things for me 1) You want to export chart sheets which end in "Chart" 2) You want all those charts in 1 PDF or separate pdfs?

[datacentric]

@ Siddharth Rout: Based on all the responses to my question, I finally figured out the code. Hide all unnecessary sheets ("Worksheets") and then export workbook (remaining chart sheets) into one PDF. I have addded the corrected code in my question as a refernce for other people. Thank you so much. :) Appreciate all your help!

[Siddharth Rout]

Your "Final Corrected Code" is incorrect. 1) ThisWorkbook.Worksheets will never loop through your chart sheets. 2) Instr is the wrong way to check if the chart sheet name ends in "Chart"

[Siddharth Rout]

Please see my previous to previous comment. I asked you two questions :)

[datacentric]

@ Siddharth: 1. Yes, I want to export Chart sheets that ends with name "Chart". 2. I want all those charts in one PDF and the name of the PDF should be the "original" file name. (I will have to save the final PDF files in different location so there will be no overlapping of files.)

[Siddharth Rout]

Gimme few minutes. Updating my answer. Will also test my code before posting.

[datacentric]

Siddharth, you are great :). This worked perfect, exactly what I want. :).. Thanks again for all your help. Have a good one!

[datacentric]

One last thing. Can you recommend me a book to start with learning VBA?

[Siddharth Rout]

Why buy a book when there are so many free tutorials online? ;) MSDN covers VBA in a very comprehensive manner. But if you still want to buy a book then checkout books by John Walkenbach

[Nick.McDermaid]

errrr you post my code and accept someone else's answer? oh well.

[Siddharth Rout]

@ElectricLlama: I am sorry.. your code? the only part which is similar is ws.Visible = True part... Rest I don't see any similarity? My post further addresses the main issues between worksheets and sheets and the incorrect use of INSTR

[datacentric]

@ElectricLlama You must be kidding. Anybody who reads the original question/code and Siddharth's detailed replies will know who really answered it.

[datacentric]

@Siddharth: Thanks. I will check out those resources. :)

[Nick.McDermaid]

Siddharth don't take it personally - I wasn't saying you posted my code I was saying datacentric posted my code in his original answer! Apologies for any miscommunication. By all means Siddharth took a lot of time and detailed effort to answer your question. For clarity I suggest you take my code out of your original question if you don't feel it met your needs as it will mislead anyone who needs an answer to this question.