Export some sheets from Excel workbook to PDF
I am working on writing VBA code to export some sheets to Excel to the same PDF. I have several chart sheets in my excel file, each ending with "(name) _Chart". I want to export all sheets with names ending in diagram 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 does not restrict exports to chart sheets only, but will export your entire workbook. Can anyone help me with the shape what is missing in my code.
Thank!
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
source to share
I'm surprised your code works in the first place :) You should have gotten the error run time error '13', type mismatch
Sheets
and Worksheets
- two different things in Excel
A collection Worksheets
is a collection of all Worksheet objects in a specified or active workbook. Each Worksheet object is a worksheet. On the other hand, a collection Sheets
consists not only of a set of sheets, but also for other types of sheets, which include chart sheets, Excel 4.0 macros, and Excel 5.0 dialog boxes.
So if you declare your object as Worksheet
Dim s As Worksheet
Then make sure you are doing the correct selection during the loop
For Each s In ThisWorkbook.Worksheets
but not
For Each s In ThisWorkbook.Sheets
else you get run time error '13', type mismatch
FOLLOWUP (based on comments)
@Siddharth: 1. Yes, I want to export chart sheets that end with the name "Chart". 2. I want all these diagrams to be in one PDF file, and the name of the PDF file must be the "original" file name. (I will need to save the final PDFs elsewhere so there is no file overlap.) - 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
source to share
This code will scan all sheets. If the sheet name doesn't match, it will hide it. When finished, it will export all visible sheets to one PDF file. Make sure yuo won't save the Excel file afterwards or the sheets won't be hidden.
Of course, this code is not validated, so if you have questions, ask (or try to figure out for yourself how you can learn something)
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") = 0 Then
' Hide the sheet so it is not exported as PDF
s.Visible = False
End If
Next s
' Export all sheets as PDF
ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & "TEST.pdf"
End Sub
source to share