Export numerous worksheets within a workbook as PDF format

[Randy11]

Located this simple line for exporting a excel workbook into a pdf format. What I would like to do is export only certain worksheets into black & white PDF format if possible. Assistance appreciated.

ActiveWorkbook.ExportAsFixedFormat xlTypePDF, buildSaveDest

Goals for code: (failing code below)
1) Export searchable portions of daily workbook in pdf file format (prefer black & white to reduce file size) so use of find feature is possible to locate certain data in file
2) Significantly reduce file size for storage
3) Ability to code in where files to be saved. i.e. C:\Track\Tracing Archive
4) Save new file name same as current excel file name with the pdf extension i.e. Track Sept 6, 2011 Teller 1.pdf
5) Simple one button click to export the desired worksheets into the pdf file.
6) Leave Excel file open & produce message box indicating Archive file has generated successfully.
7) Sample worksheets to export are named 34, 43, 9400 & will be other so understanding how to embed additional sheets in the code beneficial an alternative to name a range of sheets would also be benificial.

Code below is 1st attempt but failing.
Sub CreateDailyTraceFile()

' Export specific worksheets within workbook to pdf file format

On Error Resume Next

Application.ScreenUpdating = False

ChDrive "C"
ChDir "C:\Track\Tracing Archive"

ActiveWorksheets ("34", "43", "9400").ExportAsFixedFormat xlTypePDF
ActiveWorksheets("34", "43", "9400").SaveAs Filename:="Current File Name" & (.pdf)

MsgBox "Current Day Tracing Files Have Generated"

ActiveWorkbook.Close False

Application.ScreenUpdating = True

Exit Sub

 

[SkipVought]

hi,

Code below is 1st attempt but failing.

Please explain what is failing in detail.

Remove the On Error Resume Next and what happens?

Do you get an error message?

On what statement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Compile error sub or function not defined

1st ActiveWorksheets highlighted

Also caught my error of Exit Sub at the end, should have been End Sub. Same error occurs when fixed.

 

[SkipVought]

try

 Sheets(Array("34", "43", "9400")).ExportAsFixedFormat xlTypePDF

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Run time error 9, Subscript out of range, 1st row with Sheets(Array... highlighted.
FYI, also changed the Sheet names to the actual Sheets, i.e ("Sheet1","Sheet5","Sheet6")

 

[SkipVought]

Worksheet.ExportAsFixedFormat Method
Exports to a file of the specified format.

It appears that the object reference for this method is a worksheet, not worksheets.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Why not COPY the sheets you want in the pdf, into a workbook and then use that method on that workbook to publish.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Sometimes it seems the forest is in the way.... Thanks Skip, this is likely a much easier path. Will do some testing....
Cheers!