Macro for pdf

[pklimchuk]

Hello


I need a Macro that would Copy each Worksheet in a Workbook into a Separate PDF file and sale to a Folder

 

[Domenic]

Make sure that the workbook containing the worksheets is the active workbook, then try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] WorksheetsToPDF()

    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] strDestPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    strDestPath = "C:\Users\Domenic\Desktop\"  [color=green]'change the destination path accordingly[/color]
    
    [color=darkblue]If[/color] Right(strDestPath, 1) <> "\" [color=darkblue]Then[/color] strDestPath = strDestPath & "\"
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        strFile = strDestPath & wks.Name & ".pdf"
        wks.ExportAsFixedFormat xlTypePDF, strFile
    [color=darkblue]Next[/color] wks
    
    MsgBox "Completed...", vbInformation
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Note that the ExportAsFixedFormat method has other parameters. Have a look at the help file for details.

 

[pklimchuk]

Domenic,


Thank you Thank you worked perfectly.

 

[Domenic]

You're very welcome! Thanks for the feedback!

 

[pklimchuk]

Is it anyway Possible when the File is saved show a date after the Files Name for the PDF , Date has to be prior month.

EX Book 7-2011

 

[Domenic]

Try the following changes in red...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] WorksheetsToPDF()

    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] strDestPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=red]Dim strDate As String[/color]
    
    strDestPath = "C:\Users\Domenic\Desktop\"  [color=green]'change the destination path accordingly[/color]
    
    [color=darkblue]If[/color] Right(strDestPath, 1) <> "\" [color=darkblue]Then[/color] strDestPath = strDestPath & "\"
    
    [COLOR="Red"]strDate = Format(DateAdd("m", -1, Date), "m-yyyy")[/COLOR]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        [COLOR="Red"]strFile = strDestPath & wks.Name & " " & strDate & ".pdf"[/COLOR]
        wks.ExportAsFixedFormat xlTypePDF, strFile
    [color=darkblue]Next[/color] wks
    
    MsgBox "Completed...", vbInformation
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[pklimchuk]

Again thank thank you

 

[Domenic]

You're very welcome!

 

[pklimchuk]

Domenic

I got a new computer and now the macro will not work. Getting an error massage.

Run Time Error '1004':
document not saved. the document may be open, or an error many have been encountered when saving.

When I hit "DEBUG"

IT Points to;

wks.ExportAsFixedFormat xlTypePDF, strFile

 

[Xander117]

Just out of curiosity, because you said you got a new computer, you do have adobe reader or something installed right? I was just wondering because of where the debug is hitting.