Need VBA code for the below..

[vinu]

Hello Friends,


I need a Macro code which should do the below function in the opened workbook. Please help me out:


# Go to each sheet and convert as PDF file and save the same in the same path. PDF file should be named as "B1" cell content in the worksheet.

# The above function shuld perform in all the sheets(sheets are variable based on workbook) except 1st sheet.

# And should give a msg box saying" this many no. of sheets converted to PDF"


Thanks in advance...


Regards


Vinu

 

[Gamnis]

I would like this functions as well

 

[Hui]

vinu

Do you want to save the Entire Sheet or just certain part of it eg: Current Print area or a Cell Range ?

 

[vinu]

Hui,


only current print area..


Thanks,

Vinu

 

[Hui]

Vinu


try this

[pre]

Sub Print_PDF()

'
' by Hui 2010
'
Dim Count As Integer
Dim i As Integer
Dim Msg As String
Dim Direct As String
Dim FName As String

Direct = "C:PDF Files"    'What Directory do you want the PDF's saved in

Count = 0

For i = 2 To Sheets.Count   'setup a loop through all the sheets
' Note this doesn't check to see if there are any CHART Sheets

Sheets(i).Activate   'Select the sheet
Range("Print_Area").Activate   'Select the active Print Area

FName = Range("B1").Text + ".xlsm"   ' set the PDF File name from B1

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Direct + "" + FName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 'Print

Count = Count + 1

Next i

Msg = Str(Count) + " PDF Sheets Saved"
Msg = Msg + Chr(10)
Msg = Msg + "in " + Direct

MsgBox Msg

End Sub

[/pre]

This works for me with Excel 2007 SP3

Can't vouch for any other Excel versions as I know ExportAsFixedFormat was new in 2007

 

[vinu]

Hi Hui,


Its working if I click on F8 by going each code. If I run clicking F5 its thowing error. Run time error'1004': Method range of object'_global'failed.


If i Debug.. its highlighting the below code:

Range("Print_Area").Activate 'Select the active Print Area


Regards,

Vinu.

 

[Hui]

You need to define a Print Area on each page

Goto each Page

Select the appropriate area

Set Print Area