brent.fraser
07-10-2013, 12:04 PM
Hey all,
I am working on a "save Excel to PDF" and I want the PDF to be one page wide by how many pages long doesn't matter to me.
I have the save Excel to PDF part down but it is saving my document NOT one page width. I have set the print area on the Excel sheet.
Here's the code:
Option Explicit
Sub SheetsAsPDFsAllPromotions1PageWide()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = "G:\Tech Writing Stuff\Templates\Project Services\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & Format(Date, "MM-DD-YYYY")
With Application
.ScreenUpdating = False
.DisplayAlerts = False
On Error Resume Next '<< a folder exists
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesWide = 1
End With
'save book in this folder
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFilePath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
.Close SaveChanges:=False
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub
Is there something I am missing? I even have .PageSetup.FitToPagesWide = 1
Thanks in advance peoples.
Brent
I am working on a "save Excel to PDF" and I want the PDF to be one page wide by how many pages long doesn't matter to me.
I have the save Excel to PDF part down but it is saving my document NOT one page width. I have set the print area on the Excel sheet.
Here's the code:
Option Explicit
Sub SheetsAsPDFsAllPromotions1PageWide()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = "G:\Tech Writing Stuff\Templates\Project Services\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & Format(Date, "MM-DD-YYYY")
With Application
.ScreenUpdating = False
.DisplayAlerts = False
On Error Resume Next '<< a folder exists
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesWide = 1
End With
'save book in this folder
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFilePath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
.Close SaveChanges:=False
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub
Is there something I am missing? I even have .PageSetup.FitToPagesWide = 1
Thanks in advance peoples.
Brent