Need code for adding a range on second page ( Excel export to PDF code)

[mazher]

I am using this code and is working fine

Sub Print_PDF()
    Dim ThisFile As String
    Dim ThisPath As String
    
    With Worksheets("Autoclinics")
    
        With .PageSetup
            .RightFooter = "Printed on &D at  &T"
            .PrintQuality = 600
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperA4
        End With
    
          
            
            ThisFile = .Range("AB2").Value
            ThisPath = .Range("AA2").Value
            
        
            .Range("D3:R40").ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   Filename:=ThisPath & ThisFile & ".pdf", _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
        
        
        Application.StatusBar = False
        
    End With
    
      
End Sub

Please can you help me adding the second page in the above .pdf
The range for the second page is T3:Z21.

I will be extremely thankful to the VBA gurus.

 

[Domenic]

First, set your print areas, and then use the ExportAsFixedFormat method of the Worksheet object, instead of the Range object...

Sub Print_PDF()
    Dim ThisFile As String
    Dim ThisPath As String
    
    With Worksheets("Autoclinics")
    
        With .PageSetup
            .RightFooter = "Printed on &D at  &T"
            .PrintQuality = 600
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperA4
            .PrintArea = "D3:R40,T3:Z21"
        End With
    
          
            
            ThisFile = .Range("AB2").Value
            ThisPath = .Range("AA2").Value
            
        
            .ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   Filename:=ThisPath & ThisFile & ".pdf", _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
        
        
        Application.StatusBar = False
        
    End With
    
      
End Sub

Hope this helps!

 

[mazher]

Extremely thankful Domenic for your help and reply, its working but I have a little problem.

As the range is T3:Z21 is small as compared to D3:R40, its shrinking it to fit the page and its making it very difficult to read,

Is there are way in which the range T3:Z21 can be converted to PDF without scaling, by making some changes in the code.

Thanks is advance.

 

[Domenic]

Try setting the Zoom property of the PageSetup object to False...

.Zoom = False

 

[mazher]

Thanks Domenic,
I am now using the following code and still the page 2 in the exported pdf is too small/ shrinked

        With .PageSetup
            .RightFooter = "Printed on &D at  &T"
            .PrintQuality = 600
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperA4
            .PrintArea = "D3:R40,T3:Z21"
            .Zoom = False
        End With

 

[Domenic]

Sorry, that's my mistake, it should be...

.Zoom = 100

Does this help?

 

[mazher]

Thanks for your reply Domenic,

On changing the code to .Zoom = 100, the pages exported increased to 7 it it messed up so I have reverted back.

Just to clarify a bit more, I need to make the .zoom = 100 only for the printa area T3:Z21

For the print area D3:R40 i need to be fit on on page and the total out ie pdf will be be two pages.

Hope I am able to explain my self.

Thanks in advance for your time and help.

 

[mazher]

Unfortunately it making the size bigger for both the ranges when exported to pdf number of pages increase to 9
I need the code .zoom = 100 applied to to only the range T3:Z21

D3:R40 First page (fit to one page)
T3:Z21 Second page (.Zoom = 100)

Hope I am able to explain myself.

Thanks in advance.

 

[Domenic]

In that case, I would suggest that you copy and paste each range to separate worksheets in a newly created temporary workbook, then set the properties for each worksheet, and then export the workbook to PDF. Maybe something like this...

Option Explicit

Sub Print_PDF()

    Dim ThisFile As String
    Dim ThisPath As String
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim TempWb As Workbook
   
    Application.ScreenUpdating = False
           
    With Worksheets("Autoclinics")
   
        ThisFile = .Range("AB2").Value
        ThisPath = .Range("AA2").Value
       
        Set Rng1 = .Range("D3:R40")
        Set Rng2 = .Range("T3:Z21")
       
    End With
       
    Set TempWb = Application.Workbooks.Add(xlWBATWorksheet)
   
    With TempWb
       
        With .Worksheets(1)
            Rng1.Copy .Range("a1")
            With .PageSetup
                .RightFooter = "Printed on &D at  &T"
                .PrintQuality = 600
                .CenterHorizontally = True
                .CenterVertically = False
                .Orientation = xlLandscape
                .Draft = False
                .PaperSize = xlPaperA4
                .Zoom = False
                .FitToPagesTall = 1
                .FitToPagesWide = 1
            End With
        End With
   
        With Worksheets.Add(after:=Worksheets(1))
            Rng2.Copy .Range("a1")
            With .PageSetup
                .RightFooter = "Printed on &D at  &T"
                .PrintQuality = 600
                .CenterHorizontally = True
                .CenterVertically = False
                .Orientation = xlLandscape
                .Draft = False
                .PaperSize = xlPaperA4
                .Zoom = 100
            End With
        End With
           
       
        .ExportAsFixedFormat _
                   Type:=xlTypePDF, _
                   Filename:=ThisPath & ThisFile & ".pdf", _
                   Quality:=xlQualityStandard, _
                   IncludeDocProperties:=True, _
                   IgnorePrintAreas:=False, _
                   OpenAfterPublish:=False
                 
        .Close savechanges:=False
       
    End With
   
    Application.ScreenUpdating = True
     
End Sub