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

mazher

Active Member
Joined
Nov 26, 2003
Messages
310
I am using this code and is working fine

VBA Code:
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
First, set your print areas, and then use the ExportAsFixedFormat method of the Worksheet object, instead of the Range object...

VBA Code:
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

Active Member
Joined
Nov 26, 2003
Messages
310
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try setting the Zoom property of the PageSetup object to False...

VBA Code:
.Zoom = False
 

mazher

Active Member
Joined
Nov 26, 2003
Messages
310

ADVERTISEMENT

Thanks Domenic,
I am now using the following code and still the page 2 in the exported pdf is too small/ shrinked
VBA Code:
        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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Sorry, that's my mistake, it should be...

VBA Code:
.Zoom = 100

Does this help?
 

mazher

Active Member
Joined
Nov 26, 2003
Messages
310

ADVERTISEMENT

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

Active Member
Joined
Nov 26, 2003
Messages
310
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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...

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,528
Messages
5,596,688
Members
414,087
Latest member
chewsters

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
Top