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
Best Answer
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
andWorksheets
are two different things in ExcelThe
Worksheets
collection is a collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet. Whereas theSheets
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
Then ensure that while looping you loop through the correct collection
and not
else you will get a
run time error '13', type mismatch
FOLLOWUP (Based on Comments)