Sep 16 2021 05:33 AM
Hello,
Im trying to create a VBa code that will print from a variable range. Currently in a sheet there are 50 pages (templates) that im printing. If there a way to print inly the pages that have data in them, i was looking to possibly add a countif so that if 35 pages of data have information only 35 pages will print.
for the time being i have a created 50 print statements and a call statement to print all.
Please see below for the current code & and thanks a lot for any help.
Sub print_page_AD_1()
Worksheets("Certificate Template - AD").Activate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveSheet.Range("B1").Value, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
From:=1, _
To:=1, _
OpenAfterPublish:=False
End Sub
Sub print_page_AD_2()
Worksheets("Certificate Template - AD").Activate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveSheet.Range("B57").Value, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
From:=2, _
To:=2, _
OpenAfterPublish:=False
End Sub
Sub print_page_AD_3()
Worksheets("Certificate Template - AD").Activate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveSheet.Range("B113").Value, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
From:=3, _
To:=3, _
OpenAfterPublish:=False
End Sub
Sub print_page_AD_4()
Worksheets("Certificate Template - AD").Activate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveSheet.Range("B169").Value, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
From:=4, _
To:=4, _
OpenAfterPublish:=False
End Sub
---------------------------------------------------------------
Sub Print_all_AD()
Call print_page_AD_1
Call print_page_AD_2
Call print_page_AD_3
Call print_page_AD_4
Call print_page_AD_5
Sep 16 2021 10:45 AM
Try this:
Sub ExportPages()
Dim wsh As Worksheet
Dim n As Long
Dim i As Long
Set wsh = Worksheets("Certificate Template - AD")
n = wsh.PageSetup.Pages.Count
For i = 1 To n
wsh.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=wsh.Range("B" & 56 * (i - 1) + 1).Value, _
From:=i, To:=i
Next i
End Sub