pdf all sheets...

[DeWaal Le Grange]

Hi excel expersts

needs some help with something

the following code conferst only the current sheet that i am working on to pdf, with some support of Cute pdf writer.

Sub PDF_Sheet()
Dim Filename As String

With ActiveSheet

Filename = .Range("A1")
.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

SendKeys Filename & "{ENTER}", False
End With

End Sub

what i need is a vba code that will confert all my sheets in the workbook to pdf.

please if someone can help me, it would be greatly appreciated.

thank you

DeWaal

 

[SydneyGeek]

Hi DeWaal,

That looks like a South African address. Am I right?

To print all sheets to separate files, loop through them. Example...

Sub PDF_Sheet()
  Dim Filename As String
  Dim Sht as WorkSheet

  For Each Sht In ActiveWorkbook.WorkSheets
    Sht.Activate
    With ActiveSheet

      Filename = .Range("A1")
      .PrintOut Copies:=1, ActivePrinter:= _
      "CutePDF Writer on CPW2:", Collate:=True

      SendKeys Filename & "{ENTER}", False
    End With
  Next Sht

End Sub

Denis

 

[DeWaal Le Grange]

Hi There Sydney

Yip you are correct, i am all the way in SA... Looks like you are my neighbour in Australia.

Sydney, i have tried something simular to what you have gave me... what i need it to do is to convert all the sheets, but automaticaly save it on the desktop and save it under its tab name.

do you think this is possible?

thank you for your help

DeWaal

 

[SydneyGeek]

Hi De Waal,

If you want to change the file name to the sheet name you could use

Filename = .Name

To specify a full path, you'll have to work out what the path is first (Desktop etc. are in different locations for different operating systems).

1. Save a file to the desktop
2. Alt+F11 to go to the code window
3. In the Immediate window (Ctrl+G to display) type this:
?Activeworkbook.Path
4. Press {ENTER} and the path for the desktop will appear in the next line. In my case it's C:\Users\Denis\Desktop

Back to the code -- use this instead for the file location:

Filename = "C:\Users\Denis\Desktop\" & .Name & ".pdf"

Don't forget the final backslash.

As for knowing about Alberton, I was born in Jo'burg.

Denis