PDA

View Full Version : Change sheet to PDF & Delete Sheet



sooty8
11-19-2009, 08:38 AM
Hi Experts

I'm saving an Excel Sheet to a folder on the desktop using the code below
is it possible that when the sheet is saved to the folder it can be converted to a PDF file in the same folder and then delete the Excel Sheet
from the folder.



Sub SaveSheet()

Dim pcp As String
Application.ScreenUpdating = False
Dim iRow As Long
Dim ws As Worksheet
pcp = Range("C2").Value
Set ws = Worksheets("Year Names")
Sheets("Year Names").Copy
ActiveSheet.Name = pcp
ActiveSheet.SaveAs Filename:="C:\Documents And Settings\Sooty8.SOOTY-5A4051ECC\Desktop\2010 Names\ " & pcp

ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub


Many Thanks

Sooty8

RolfJ
11-19-2009, 04:45 PM
It would probably be much easier to print the worksheet to a PDF printer (e.g. http://www.printgreener.com/) from within Excel. Please let me know if you need help with that.

GreenTree
11-19-2009, 05:59 PM
Check out the following article (and those that it links to):
http://www.excelguru.ca/node/21
Ken is a frequent contributor here, and the code in these articles is first-rate. Worked for me with only the slightest of tweaking to adapt it to my project.

Best of luck,

G.T.

sooty8
11-20-2009, 02:34 AM
Hi Guys

Thanks for the info will have a look at it over the weekend.

Many Thanks

Sooty8