2007 Excel saveas PDF with VBA

[Tmann]

I was hoping someone could help me with a problem I am facing.
I currently have an Excel 2007 workbook containing 47 worksheets. I need to save each individual worksheet to its own PDF. Some of the worksheets in the workbook are blank and one of the worksheets contains information that does not need to be saved to PDF.

Can someone please help me with any VBA code I can use to accomplish my goal, I have hit a dead end and don't know where to start.

 

[pgc01]

Hi

You just have to loop through the worksheet list and export them individually.

For ex., this code would do it for 3 worksheets:

Sub Macro1()
Dim wsh As Worksheet, vWshs, vWshName

vWshs = Array("Sheet1", "Sheet3", "Sheet4")
With ActiveWorkbook
    For Each vWshName In vWshs
        .Worksheets(vWshName).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Tmp\" & vWshName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next vWshName
End With
End Sub

 

[Tmann]

Thank you for your response, it was a great help. If you don't mind I would like to pick your brain just a little bit more. This workbook can grow in size with the addition of worksheets and also worksheets can be deleted. Is there anyway in the code to have it know how many worksheets there are to save to PDF and also to exclude certain worksheets that I do not need to save?

 

[pgc01]

Ok, this does the opposite, it exports all the worksheets except the ones in the list.

Sub Macro1()
Dim wsh As Worksheet, vWshs
 
vWshs = Array("Sheet1", "Sheet3", "Sheet4")
 
For Each wsh In ActiveWorkbook.Worksheets
    If IsError(Application.Match(wsh.Name, vWshs, 0)) Then
        wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Tmp\" & wsh.Name, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End If
Next wsh
End Sub

 

[Tmann]

Thank you again, One mroe quick questions, is it possible to have the code check for data in a specific cell and if the data exists in that cell for it to save those files where the data is present to PDF. Sorry for all the questions.

 

[pgc01]

Assuming you write the word "Export" in cell A1 of the worksheets you want to export, replace

    If IsError(Application.Match(wsh.Name, vWshs, 0)) Then

with.

    If LCase(wsh.Range("A1")) = "export" Then