Export a range to pdf

Matt1989

New Member
Joined
Aug 14, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Afternoon

I am looking to export a number of ranges to one single PDF but one of the ranges is a variable range. The ranges I want to export are:

A1:C12
A13:C26
A27:C38
A42:C72
A73:C95
G161: the last cell with a text in it located in Column K

the file name is: thisworkbook.path & “Reports” & “\” & TextBox8.Text

If possible I would be looking to export the first 5 ranges as portrait and the last variable range in Landscape

If anyone can help that would be great
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
The way I do it is by copying the sheet with all the data and give it a new name
Set print ranges and orientation on both sheets and insert horizontal pagebreaks on the 1st sheet where required.
Hide the rows/columns on both sheets that do not need to be printed
Select both sheets and print to PDF.
Delete the 2nd sheet and unhide rows in 1st sheet
If you need help with this, let us know and we'll be happy to do so.
 
Upvote 0
See if you can adapt the code in this thread for a similar request:
 
Upvote 0
Try this as a starter and see if it comes close to what you would like to see.
Code:
Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long
Dim pbArr, shArr, PDF As String
pbArr = Array(12, 26, 38, 72, 95)
Set sh1 = Worksheets("Sheet1")    '<----- Sheet with all the data. Change name as required
sh1.Copy After:=sh1
ActiveSheet.Name = "Temp"
Set sh2 = Worksheets("Temp")
shArr = Array("Sheet1", "Temp")    '<----- Change "Sheet1" name as required
PDF = ThisWorkbook.Path & "\Reports " & sh1.Range("H1").Text & ".PDF"   '<---- Change as required
With sh1
    .PageSetup.PrintArea = Range("A1:C95").Address
    .PageSetup.Orientation = xlPortrait
    .ResetAllPageBreaks
        For i = LBound(pbArr) To UBound(pbArr)
            .HPageBreaks.Add Before:=.Range("A" & pbArr(i) + 1)
        Next i
    .Rows("39:41").Hidden = True
End With
With sh2
   .PageSetup.PrintArea = .Range("G161:K" & .Cells(.Rows.Count, "K").End(xlUp).Row).Address
   .PageSetup.Orientation = xlLandscape
End With
Sheets(shArr).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
    Application.DisplayAlerts = False
        Sheets("Temp").Delete
    Application.DisplayAlerts = True
sh1.Columns("A:A").EntireRow.Hidden = False
End Sub
 
Upvote 0
Try this as a starter and see if it comes close to what you would like to see.
Code:
Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long
Dim pbArr, shArr, PDF As String
pbArr = Array(12, 26, 38, 72, 95)
Set sh1 = Worksheets("Sheet1")    '<----- Sheet with all the data. Change name as required
sh1.Copy After:=sh1
ActiveSheet.Name = "Temp"
Set sh2 = Worksheets("Temp")
shArr = Array("Sheet1", "Temp")    '<----- Change "Sheet1" name as required
PDF = ThisWorkbook.Path & "\Reports " & sh1.Range("H1").Text & ".PDF"   '<---- Change as required
With sh1
    .PageSetup.PrintArea = Range("A1:C95").Address
    .PageSetup.Orientation = xlPortrait
    .ResetAllPageBreaks
        For i = LBound(pbArr) To UBound(pbArr)
            .HPageBreaks.Add Before:=.Range("A" & pbArr(i) + 1)
        Next i
    .Rows("39:41").Hidden = True
End With
With sh2
   .PageSetup.PrintArea = .Range("G161:K" & .Cells(.Rows.Count, "K").End(xlUp).Row).Address
   .PageSetup.Orientation = xlLandscape
End With
Sheets(shArr).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
    Application.DisplayAlerts = False
        Sheets("Temp").Delete
    Application.DisplayAlerts = True
sh1.Columns("A:A").EntireRow.Hidden = False
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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