Saving excel tabs as PDF.

New_Keith

New Member
Joined
Jun 14, 2018
Messages
7
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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 ...

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


This would save the pdf to your desktop.
 
Upvote 0
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.


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

Code:
[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]
 
Upvote 0
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
 
Upvote 0
Thanks its now coming up with Run-time error 5: Invalid Procedure call or argument and highlights the part below:

Code:
.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
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top