I have read a few of your posts but not sure if any of those implied to what
I needed. Here's my question

I have about 20 worksheets/tabs in my workbook. I want to print/save them
in pdf format. All will be saved in the same location but each will have its
own file name base on the tab name (Dept A, Dept B, Dept C, etc). Can this
be possibly done? Please help.

Thanks,
ml

Assuming you are running Excel 2007

Sub Save_Sheets_As_PDF()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Program Files\Microsoft Office\2007exceldata" _
& "\" & w.Name & ".pdf"
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:27:01 -0700, ml wrote:

I have read a few of your posts but not sure if any of those implied to what
I needed. Here's my question

I have about 20 worksheets/tabs in my workbook. I want to print/save them
in pdf format. All will be saved in the same location but each will have its
own file name base on the tab name (Dept A, Dept B, Dept C, etc). Can this
be possibly done? Please help.

Thanks,
ml

Thank you so much! Works like a charm.

"Gord Dibben" wrote:

Assuming you are running Excel 2007

Sub Save_Sheets_As_PDF()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Program Files\Microsoft Office\2007exceldata" _
& "\" & w.Name & ".pdf"
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:27:01 -0700, ml wrote:

I have read a few of your posts but not sure if any of those implied to what
I needed. Here's my question

I have about 20 worksheets/tabs in my workbook. I want to print/save them
in pdf format. All will be saved in the same location but each will have its
own file name base on the tab name (Dept A, Dept B, Dept C, etc). Can this
be possibly done? Please help.

Thanks,
ml

Glad to help.

Thanks for the feedback


Gord

On Mon, 13 Jul 2009 09:55:01 -0700, ml wrote:

Thank you so much! Works like a charm.

"Gord Dibben" wrote:

Assuming you are running Excel 2007

Sub Save_Sheets_As_PDF()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Program Files\Microsoft Office\2007exceldata" _
& "\" & w.Name & ".pdf"
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 06:27:01 -0700, ml wrote:

I have read a few of your posts but not sure if any of those implied to what
I needed. Here's my question

I have about 20 worksheets/tabs in my workbook. I want to print/save them
in pdf format. All will be saved in the same location but each will have its
own file name base on the tab name (Dept A, Dept B, Dept C, etc). Can this
be possibly done? Please help.

Thanks,
ml