vandango05
Board Regular
- Joined
- Oct 6, 2008
- Messages
- 110
Good day all.
I'm using the code below to be able to create a pdf from multiple worksheets with different print areas as one file. The issue I have is that the array which determines which sheets to print as well as the print areas are different each time. It will be fairly easy for me to create a formula to create the array and print areas in a worksheet called "Summary" (Sheet32) in say cells A1 for the Array and A2 for the print area.
Is there a way i can reference the values in these cells in the code?
I'm using the code below to be able to create a pdf from multiple worksheets with different print areas as one file. The issue I have is that the array which determines which sheets to print as well as the print areas are different each time. It will be fairly easy for me to create a formula to create the array and print areas in a worksheet called "Summary" (Sheet32) in say cells A1 for the Array and A2 for the print area.
Is there a way i can reference the values in these cells in the code?
Code:
Sub printmultiplesheets()
Dim path As String
Dim myArr As Variant, a As Variant
Dim rngArr As Variant
Dim Ws As Worksheet
Dim i As Integer
myArr = Array("Contract 1st Page", "Windows", "Doors") '<~~ Sheet names
rngArr = Array("C1:K57", "B2:BX95", "B2:BX111") '<~~ print area addresses
For i = 0 To UBound(myArr)
Set Ws = Sheets(myArr(i))
With Ws
.PageSetup.PrintArea = .Range(rngArr(i)).Address
End With
Next
Sheets(myArr).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Sheet32.Range("S18").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub
Last edited: