Print single PDF using Multiple Worksheets whose names are read from range of cells

Old_FORTRAN_Guy

New member
Joined
Sep 26, 2014
Messages
2
Reaction score
0
Points
0
I'm an FORTRAN programmer from way back but I'm new to VB. I have a Workbook which has many worksheets for invoicing. I want to automate creating a single PDF file from multiple worksheets.
The worksheet names need to vary each time I run the macro. Here is the code. Sorry for all the comments.

Sub Print_OSARC_PDF()

'I started out with the following code which has a list of worsheets hard coded in the Sheets line.
' These 2 lines with the hard coded worksheet names work but I need to be able to change the worksheets names and order
' every time I run the Macro.
'ThisWorkbook.Sheets(Array("Adams CAD", "Choctaw CAD", "Franklin CAD", "Hinds CAD", "Madison CAD", "Noxubee CAD", "Oktibbeha CAD", "Webster CAD", "Wilkinson CAD", "Yazoo CAD")).Select
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Myfilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
'The above 2 lines work fine if all other code is commented out.

' To do what I need, I propose to have the code read the Worksheet names from a range of cells (Cells C4:C34)
' on a reserved macro sheet (in mine it's called "PRINTPDF") with the code executed from a button on that sheet.

'Define variable types
Dim Myfilename As String
Dim Mylist As String
Dim Listval As Variant
Dim msg As String
Dim sSheets() As String

'Initialize variables
Myfilename = "Submit" 'The PDF filename will be Submit.pdf
Mylist = ""
msg = " Will print to PDF file the following worksheets: "

'Populate Listval array with values in cells on macro worksheet
Listval = Range("C4:C34").Value
'Build a text string called "Mylist" of worksheet names separated by commas
'to be passed along to the pdf print routine.
For i = LBound(Listval) To UBound(Listval)
'MsgBox Listval(i, 1)
If Listval(i, 1) <> "" Then
Mylist = Mylist & Listval(i, 1) & ","
End If
Next

'Build a text string to display in the message box
msg = msg & vbCr & Mylist
MsgBox msg
' This next line of code did not work
'ThisWorkbook.Sheets(Mylist).Select

' So I next tried to use the Split command in a String array called sSheets.
sSheets() = Split(Mylist, ",")
' I turned on Watches in the Developer window for sSheets and they load with the correct values

' My logic below to try and use the Sheets command with .Select does not work
'Sheets(sSheets()).Select

' If either of the above would have worked, I would have try to pass it to the 2 lines below using "Selection" instead of "ActiveSheet" like the code at the top
'Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Myfilename & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
if all the cells in the range had a valid sheet name then this would have done:
Code:
Sub blah()
Sheets(Application.Transpose(Range("C4:C34"))).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Submit.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
but since you're also checking or blanks in the range then, as long as they're all valid sheet names this should do it:
Code:
Sub blah2()
For Each cll In Range("C4:C34").SpecialCells(xlCellTypeConstants, 2).Cells
  If FirstSheetSelected Then
    Sheets(cll.Value).Select False
  Else
    Sheets(cll.Value).Select
    FirstSheetSelected = True
  End If
Next cll
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\Submitf.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
It assumes the sheet names are plain text (not the result of a formula) and the other cells are truly blank. If these do not pertain, then come back and I'll offer a tweak. I can also offer a tweak if you want the names of the sheets checking to see that they are valid (exist).
 

Old_FORTRAN_Guy

New member
Joined
Sep 26, 2014
Messages
2
Reaction score
0
Points
0
WOW. Thanks for the fast reply. This forum rocks! (Or as they used to say in my day "Groovy Man!) Anyway the second arrangement of code "Sub blah2" works perfectly for my needs. This really will save us all a lot of time. Thank you so much for the help.
 
Top