Export to single PDF from matrix of range and sheets

[Kieranz]

Hi,
I would like to export or print from the attached image of the Matrix of ranges, orientation and sheets to a single PDF.
I think there's arrays and loops and offsets involved and as a novice, it's making my brain fuzzy :) after a lot of googling for the last three days.

Any help most appreciated. Thks

[Logit]

Are all the sheets with the table/ranges located in the same workbook ? YES NO

Are there a set number of sheets involved (ex: Sheets 2 thru Sheets 9 ) YES NO

Do you want only the table lines with the last column showing a "y" and to not copy the rows that have a "n" showing ? YES NO

[Kieranz]

Good Morning,
1/. No, there are no tables or range names
2/. No, there are no sets of sheets involved. However, the sheet codename will follow a sequence as shown in the image.
3/. No. The user can type yes or no depending on which sheets he wishes to pdf. The control box image is where the user can select which sheets need to be pdf.

I hope that's helpful
Rgds

[Kieranz]

Sorry,
1/. A number of sheets in one workbook. The control box ie the image shown is on the first sheet where the user is able to select the sheets and print range etc by typing Y or N in the last column of the control box. The yes/no are the only cells the user can edit. The rest of the info in the control box is protected. Also using sheet codename rather than sheet tabname in the control box (the first column).
Hope that helps. Thks

[Logit]

It would help a great deal if you could post a copy of your workbook with the various sheets and their data. Then give me some time to put this together.

[Paul_Hossler]

Something like this



Option Explicit


Const cSheet As Long = 1
Const cTitle As Long = 2
Const cArea As Long = 3
Const cOrient As Long = 4
Const cYN As Long = 6




Sub PrintThem()
Dim rPrint As Range, rLast As Range, rFirst As Range, rRow As Range
Dim ws As Worksheet
Dim A() As String
Dim iA As Long
Dim sFile As String


Set rFirst = Control.Cells(5, 2)
Set rLast = Control.Cells(Control.Rows.Count, 7).End(xlUp)
Set rPrint = Range(rFirst, rLast)


For Each rRow In rPrint.Rows
With rRow
If UCase(.Cells(cYN).Value) = "N" Then GoTo NextSheet

Set ws = pvtWorksheet(.Cells(cSheet).Value)
If ws Is Nothing Then GoTo NextSheet

iA = iA + 1
ReDim Preserve A(1 To iA)
A(UBound(A)) = ws.Name

ws.PageSetup.PrintArea = ""
ws.PageSetup.PrintArea = Range(.Cells(cArea).Value).Address
ws.PageSetup.PrintTitleRows = .Cells(cTitle).Value
ws.PageSetup.PrintTitleColumns = ""
Select Case .Cells(cOrient).Value
Case "L", "l"
ws.PageSetup.Orientation = xlLandscape
Case "P", "p"
ws.PageSetup.Orientation = xlPortrait
End Select
End With

NextSheet:
Next


sFile = Application.GetSaveAsFilename(fileFilter:="PDF File (*.pdf), *.pdf")


If sFile = "False" Then Exit Sub


Sheets(A).Select


ActiveSheet.ExportAsFixedFormat Filename:=sFile, Type:=xlTypePDF, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


Control.Select


MsgBox "All Done"


End Sub


Private Function pvtWorksheet(s As String) As Worksheet
Dim ws As Worksheet

Set pvtWorksheet = Nothing

For Each ws In ThisWorkbook.Worksheets
If UCase(ws.CodeName) = UCase(s) Then
Set pvtWorksheet = ws
Exit Function
End If
Next
End Function

[Kieranz]

Good Evening
My part of the Earth (Zambia, Southern Africa), its nighttime.
To Logit and Paul thank you so much for your help. Please give me tomorrow to make up a dummy file that I can attach.
Paul, I see the thread and it's adding up. A little overwhelming!
Good night and you guys have a lovely day!
Thks
KN

[SamT]

I think I would copy the Print Area Ranges, (with a "Y",) to a single Temporary sheet (using Transpose as needed) then save that sheet to PDF.

[Paul_Hossler]

I think I would copy the Print Area Ranges, (with a "Y",) to a single Temporary sheet (using Transpose as needed) then save that sheet to PDF.

I believe that this would not work if the input sheets included both portrait and landscape orientation

[SamT]

I'm not sure what Landscape means when "Printing" to PDF in this question. Is it like printing to paper, where you have to turn the paper sideways to read it?

[Paul_Hossler]

I didn't interpret it to mean rotated, just wider, not taller, using Page Layout

28720

The PDF prints the text normally, but the orientation of the pages are a mixture of portrait and landscape

28721

Download this from #6, and re-extension it


ver2.pdf.notreallya.zip (http://www.vbaexpress.com/forum/attachment.php?attachmentid=28718&d=1625937483) (206.0 KB, 1 views)


and see what I mean

[Kieranz]

Good morning
Please find the attached file. Hope it explains better!
Thks
KN

[Paul_Hossler]

Option Explicit


Const cSheet As Long = 1
Const cCodeName As Long = 2
Const cTitle As Long = 3
Const cArea As Long = 4
Const cOrient As Long = 5
Const cYN As Long = 7




Sub PrintThem()
Dim rPrint As Range, rLast As Range, rFirst As Range, rRow As Range
Dim ws As Worksheet
Dim A() As String
Dim iA As Long
Dim sFile As String


Set rFirst = x01.Cells(9, 2)
Set rLast = x01.Cells(9, 8).End(xlDown)
Set rPrint = Range(rFirst, rLast)


For Each rRow In rPrint.Rows
With rRow
If UCase(.Cells(cYN).Value) = "N" Then GoTo NextSheet

Set ws = Nothing
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(.Cells(cSheet).Value)
On Error GoTo 0

If ws Is Nothing Then GoTo NextSheet

iA = iA + 1
ReDim Preserve A(1 To iA)
A(UBound(A)) = ws.Name

ws.PageSetup.PrintArea = ""
ws.PageSetup.PrintArea = Range(.Cells(cArea).Value).Address
If Len(.Cells(cTitle).Value) > 0 Then
ws.PageSetup.PrintTitleRows = Range(.Cells(cTitle).Value).Address
End If
ws.PageSetup.PrintTitleColumns = ""
Select Case .Cells(cOrient).Value
Case "L", "l"
ws.PageSetup.Orientation = xlLandscape
Case "P", "p"
ws.PageSetup.Orientation = xlPortrait
End Select
ws.PageSetup.FitToPagesWide = 1
ws.PageSetup.FitToPagesTall = False


ws.PageSetup.RightFooter = "Page &P of &N"
End With

NextSheet:
Next


sFile = Application.GetSaveAsFilename(fileFilter:="PDF File (*.pdf), *.pdf")


If sFile = "False" Then Exit Sub


Sheets(A).Select


ActiveSheet.ExportAsFixedFormat Filename:=sFile, Type:=xlTypePDF, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


x01.Select


MsgBox "All Done"


End Sub

[Kieranz]

Paul,
1/. "You changed the format of this control range"
I had edited the control box ranges to match the dummy file content.

2/. "Print areas must include Title Rows"
Learnt something

3/. "Why do you need Sheet Codename?"
In a few sheets the User can change the sheet tab name, so not sure how to use the sheet codename to avoid any User change.

4/. "Print one sheet is left as homework assignment"
Below is the home assignment. I hope it meets your standard. (Are you a lecturer by any chance? :yes )

Sub PrnShToPDF()
Dim ws As Worksheet, sFilenm As String

Set ws = ThisWorkbook.ActiveSheet
sFilenm = "D:\01_Docs\TestShtToPdf" & Format(Now, "HHMm") & ".pdf"

With ws
.PageSetup.PrintTitleRows = .[ab2] 'set with a constant in hidden columns
.PageSetup.PrintArea = .[ab3].Value 'set with a formula in hidden columns

If UCase(.[ab4]) = "P" Then 'set with a constant in hidden columns
.PageSetup.Orientation = xlPortrait
Else
.PageSetup.Orientation = xlLandscape
End If

.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = False
.PageSetup.RightFooter = "Page &P of &N"
End With

ActiveSheet.ExportAsFixedFormat Filename:=sFilenm, Type:=xlTypePDF, Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

ws.[e2].Select
End Sub

Paul your code was perfect. Thank you very much for your patience and help and also the other helpers.
With kind regards and respect.
KN

[Paul_Hossler]

Good

1 - if you change the x01 format, then the code needs to be changed

2 - Roger

3 - IMHO, I'd delete the Code Name from x01. If the user changes the WS name on the tab, it's on them. I think the Code Name would confuse them.
If they were to change the WS name, then it won't print. You could add an error message. NEVER trust the users to do what's expected

4 - If it works, it's good, although it'd be better to read the print parameters from x01 instead of having then on the ActiveSheet somewhere; less maintenance



Walk on

1. If you want, you can suppress the button printing e.g. [Prn to PDF] on the PDF output using Right-click and Format Shape

28725


2. Personally, I always start in A1, i.e. no blank row 1 and no blank cols A and B. Makes coding easier and avoids data getting in the wrong place

[Kieranz]

Many thanks, Paul.
Marking as solved
KN