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
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