Saving excel tabs as PDF.

[New_Keith]

Hi all,

I have a macro that works in printing out the requested fields for all items on a drop down list. It determines if there is any values on the second page then prints out just one or both pages depending on what is returned. I am trying to adjust it so it saves as PDF rather than prints. When running it i get the message come up saying all files have been saved but nothing showing in the drive.

All help gratefully received.

macro is as per below and shows the all files saved message:

Sub PDF_Saving_Macro()
'
' PDF_Saving_Macro Macro
'

Dim x As Integer
x = Application.Max(Range("Z8:Z100"))

For i = 1 To x

Range("Z6").Value = i
Calculate





Dim costcentretoprint As String
costcentretoprint = Range("AA6").Value

If Range("K3").Value <> 0 Then
MsgBox (costcentretoprint & " doesn't balance - not printed")
Else

Sheets("Template").Select
Range("b1").Value = costcentretoprint

If Range("w1").Value = "N" Then
Sheets(Array("Template", "Payroll Pivot")).Select
Else
Sheets(Array("Template")).Select
End If
Sheets("Template").Activate
SelectedSheets.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="\\filepath goes here" & Range("C2").Value & ".pdf"
Sheets("Control Sheet").Select

End If

Next i
MsgBox ("Files have been saved.")


End Sub

 

[James_Latimer]

Your message box is going to show regardless of what happens above in the code... i would assume that your Filename setting is invalid - unless you have a location on your computer called "\\file path goes here"
The file path needs to be a valid location that can be reached by your computer.

You're probably better doing something like ...

[COLOR=#333333]Filename:= Environ("USERPROFILE") & "\Desktop\" &[/COLOR][COLOR=#333333] Range("C2").Value & ".pdf"[/COLOR]

This would save the pdf to your desktop.

 

[New_Keith]

Sorry a little confused. The file path is valid just used "file path goes here" as to not release possible confidential information. & Range("C2").Value just picks up the name in the file to save it as and .PDF.

Ive just tried the code above and it just doesn't seem to be saving them anywhere. Any other ideas?

Thanks again.

 

[Logit]

.
This macro will save the ACTIVE SHEET to the same location as the workbook. Edit as required :

[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]Option Explicit[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Sub Export_Worksheet_to_PDF()[/TD]
 [/TR]
 [TR]
  [TD]   [/TD]
 [/TR]
 [TR]
  [TD]     Dim currentSheet As Worksheet, fname As String[/TD]
 [/TR]
 [TR]
  [TD]     fname = ActiveSheet.Range("C2").Value[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]     With ThisWorkbook[/TD]
 [/TR]
 [TR]
  [TD]         Set currentSheet = .ActiveSheet[/TD]
 [/TR]
 [TR]
  [TD]         .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,  Filename:=ThisWorkbook.Path & "\" & fname &  ".pdf", _[/TD]
 [/TR]
 [TR]
  [TD]            Quality:=xlQualityStandard,  IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True[/TD]
 [/TR]
 [TR]
  [TD]     End With[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[New_Keith]

Hi Logit,

Tried running it and its coming up with an expected end sub error?

Cheers,
Keith.

 

[Logit]

Sounds like the words End Sub are not the last line in the macro.

Make certain the macro begins with Sub Export_Worksheet_to_PDF() and ends with End Sub

 

[New_Keith]

Thanks its now coming up with Run-time error 5: Invalid Procedure call or argument and highlights the part below:

.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\\svrfinance01\colin\_Divisions\_Specialist Medicine\201819\Keith Workings\Cost Centre Reports\M02 Reports" & fname & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True