Hi all
I have at problem with my "save as pdf" code below. Normally it works fine, but sometimes there is a zooming problem and the range is saved with only 12% zoom in the pdf. If I change the page print zoom to something higher the problem is solved for some weeks until it happens again (I guess something triggers this, but I don't know what). It is important that the range is saved in only one page in the .pdf-file.
Any suggestions?
Sub SaveAsPDF()
'Declare variables
Dim SelectedRange As Range
Dim FileName As String
Dim i As Integer
'Save the filename, mm_xx, in the string "filename"
FileName = Left(ThisWorkbook.name, (InStrRev(ThisWorkbook.name, ".", -1, vbTextCompare) - 1))
'Select MM-sheet and select cell A1
Sheets("Market Movers").Select
Range("A1").Select
'Select the MM
Set SelectedRange = Range(Selection, ActiveCell.SpecialCells(xlLastCell))
'We have to make sure that we have actually selected the whole MarketMover
Call SelectMarketMover(SelectedRange)
i = Selection.Rows.Count
'PageBreakView so we can edit the pagebreaks (if we dont do this
'the code might not work!
ActiveWindow.View = xlPageBreakPreview
'1) (code line 1 & 2 below)
' Set the printing area way too large for the market mover
' We do this to make sure that we can set the HPageBreaks(1)
' in the right spot
'2) (code line 3)
' Set printing area to cover the market mover
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(500, 8)).Address
Set ActiveSheet.HPageBreaks(1).Location = Range("A1").Offset(i, 0)
ActiveSheet.PageSetup.PrintArea = Selection.Address
'If something went wrong - contact Mikael
If Not ActiveSheet.HPageBreaks(1).Location = Range("A1").Offset(i, 0) Then
MsgBox "Noget gik galt ved at sætte HPageBreak" & vbNewLine & "Lav pdf'en manuelt og kontakt Mikael"
End If
If Not ActiveSheet.PageSetup.PrintArea = Selection.Address Then
MsgBox "Noget gik galt ved at sætte HPageBreak" & vbNewLine & "Lav pdf'en manuelt og kontakt Mikael"
End If
'Go back to normal page view
ActiveWindow.View = xlNormalView
'Publish the MM-calendar as a .pdf file in the marketmovers folder
Range(Cells(1, 1), Cells(i, 8)).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"H:\Documents\" + FileName + ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=True, OpenAfterPublish:=True
'Publish the MM-calender as a .pdf file in the presentations folder. OpenAfterPublish
'is ste to false, because we dont want to open the pdf file twice.
Range(Cells(1, 1), Cells(i, 8)).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"H:\Documents\Marketmovers.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=True, OpenAfterPublish:=False
Range("A1").Select
'Go back to Vejledning & dato
Sheets("Vejledning & dato").Select
End Sub
Bookmarks