Forum Discussion

Jonathan865's avatar
Jonathan865
Copper Contributor
Sep 16, 2021

Excel VBA Printing from a variable range

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

 

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 16, 2021

    Jonathan865 

    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

Resources