Can i have a vba macro that creates a pdf with mixed Landscape and Portrait sheets?

[tonywatsonhelp]

Hi Everyone,

I hope you can help,
I have a document that contains 3 areas
Sheet "DATA"
Range "D10:L56" Portrait
Range "C60:R90" Landscape
Range "D95:L146" Portrait

What I would like is to make each sheet Portrait or Landscape as i need it, as shown above.
Is it posible to do this?
I can split then up onto there own sheets if this is simpler?
thanks
Tony

 

[davesexcel]

You can create sheets and use them in the print job, then delete the sheets after.

Sub PrintJobs()
    Dim ws As Worksheet
    Dim p1 As Range, p2 As Range, p3 As Range
    Dim print1 As Worksheet, print2 As Worksheet, print3 As Worksheet
    Set ws = Sheets("DATA")

    With ws
        Set p1 = .Range("D10:L56")
        Set p2 = .Range("C60:R90")
        Set p3 = .Range("D95:L146")
    End With
    Set print1 = Sheets.Add(After:=Worksheets(Worksheets.Count))
    Set print2 = Sheets.Add(After:=Worksheets(Worksheets.Count))
    Set print3 = Sheets.Add(After:=Worksheets(Worksheets.Count))
    With print1
        .Name = "print1"
        p1.Copy .Range("A1")
        .PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
        With .PageSetup
            .Zoom = False
            .Orientation = xlPortrait
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        Application.PrintCommunication = True
    End With
    With print2
        .Name = "print2"
        p2.Copy .Range("A1")
        .PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
        With .PageSetup
            .Zoom = False
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        Application.PrintCommunication = True
    End With
    With print3
        .Name = "print3"
        p3.Copy .Range("A1")
        .PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
        With .PageSetup
            .Zoom = False
            .Orientation = xlPortrait
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        Application.PrintCommunication = True
    End With
    Sheets(Array("print1", "print2", "print3")).PrintPreview 'change to printOut
    With Application
        .DisplayAlerts = False
        Sheets(Array("print1", "print2", "print3")).Delete
        .DisplayAlerts = True
    End With
End Sub

 

[tonywatsonhelp]

Brilliant, thank you Dave, but how do i get it to creae a PDF?

 

[davesexcel]

Brilliant, thank you Dave, but how do i get it to creae a PDF?

Oh ya,
Change filename

    Sheets(Array("print1", "print2", "print3")).Select

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\drrison\Downloads\Windsor.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

 

[tonywatsonhelp]

brilliant, thank you so much