Excel VBA Printing from a variable range

%3CLINGO-SUB%20id%3D%22lingo-sub-2755951%22%20slang%3D%22en-US%22%3EExcel%20VBA%20Printing%20from%20a%20variable%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2755951%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIm%20trying%20to%20create%20a%20VBa%20code%20that%20will%20print%20from%20a%20variable%20range.%20Currently%20in%20a%20sheet%20there%20are%2050%20pages%20(templates)%20that%20im%20printing.%20If%20there%20a%20way%20to%20print%20inly%20the%20pages%20that%20have%20data%20in%20them%2C%20i%20was%20looking%20to%20possibly%20add%20a%20countif%20so%20that%20if%2035%20pages%20of%20data%20have%20information%20only%2035%20pages%20will%20print.%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20the%20time%20being%20i%20have%20a%20created%2050%20print%20statements%20and%20a%20call%20statement%20to%20print%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20below%20for%20the%20current%20code%20%26amp%3B%20and%20thanks%20a%20lot%20for%20any%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20print_page_AD_1()%3C%2FP%3E%3CP%3EWorksheets(%22Certificate%20Template%20-%20AD%22).Activate%3C%2FP%3E%3CP%3EActiveSheet.ExportAsFixedFormat%20_%3CBR%20%2F%3EType%3A%3DxlTypePDF%2C%20_%3CBR%20%2F%3EFilename%3A%3DActiveSheet.Range(%22B1%22).Value%2C%20_%3CBR%20%2F%3EQuality%3A%3DxlQualityStandard%2C%20_%3CBR%20%2F%3EIncludeDocProperties%3A%3DFalse%2C%20_%3CBR%20%2F%3EIgnorePrintAreas%3A%3DFalse%2C%20_%3CBR%20%2F%3EFrom%3A%3D1%2C%20_%3CBR%20%2F%3ETo%3A%3D1%2C%20_%3CBR%20%2F%3EOpenAfterPublish%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3ESub%20print_page_AD_2()%3C%2FP%3E%3CP%3EWorksheets(%22Certificate%20Template%20-%20AD%22).Activate%3C%2FP%3E%3CP%3EActiveSheet.ExportAsFixedFormat%20_%3CBR%20%2F%3EType%3A%3DxlTypePDF%2C%20_%3CBR%20%2F%3EFilename%3A%3DActiveSheet.Range(%22B57%22).Value%2C%20_%3CBR%20%2F%3EQuality%3A%3DxlQualityStandard%2C%20_%3CBR%20%2F%3EIncludeDocProperties%3A%3DFalse%2C%20_%3CBR%20%2F%3EIgnorePrintAreas%3A%3DFalse%2C%20_%3CBR%20%2F%3EFrom%3A%3D2%2C%20_%3CBR%20%2F%3ETo%3A%3D2%2C%20_%3CBR%20%2F%3EOpenAfterPublish%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3ESub%20print_page_AD_3()%3C%2FP%3E%3CP%3EWorksheets(%22Certificate%20Template%20-%20AD%22).Activate%3C%2FP%3E%3CP%3EActiveSheet.ExportAsFixedFormat%20_%3CBR%20%2F%3EType%3A%3DxlTypePDF%2C%20_%3CBR%20%2F%3EFilename%3A%3DActiveSheet.Range(%22B113%22).Value%2C%20_%3CBR%20%2F%3EQuality%3A%3DxlQualityStandard%2C%20_%3CBR%20%2F%3EIncludeDocProperties%3A%3DFalse%2C%20_%3CBR%20%2F%3EIgnorePrintAreas%3A%3DFalse%2C%20_%3CBR%20%2F%3EFrom%3A%3D3%2C%20_%3CBR%20%2F%3ETo%3A%3D3%2C%20_%3CBR%20%2F%3EOpenAfterPublish%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3ESub%20print_page_AD_4()%3C%2FP%3E%3CP%3EWorksheets(%22Certificate%20Template%20-%20AD%22).Activate%3C%2FP%3E%3CP%3EActiveSheet.ExportAsFixedFormat%20_%3CBR%20%2F%3EType%3A%3DxlTypePDF%2C%20_%3CBR%20%2F%3EFilename%3A%3DActiveSheet.Range(%22B169%22).Value%2C%20_%3CBR%20%2F%3EQuality%3A%3DxlQualityStandard%2C%20_%3CBR%20%2F%3EIncludeDocProperties%3A%3DFalse%2C%20_%3CBR%20%2F%3EIgnorePrintAreas%3A%3DFalse%2C%20_%3CBR%20%2F%3EFrom%3A%3D4%2C%20_%3CBR%20%2F%3ETo%3A%3D4%2C%20_%3CBR%20%2F%3EOpenAfterPublish%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E---------------------------------------------------------------%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESub%20Print_all_AD()%3C%2FP%3E%3CP%3ECall%20print_page_AD_1%3CBR%20%2F%3ECall%20print_page_AD_2%3CBR%20%2F%3ECall%20print_page_AD_3%3CBR%20%2F%3ECall%20print_page_AD_4%3CBR%20%2F%3ECall%20print_page_AD_5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2755951%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2757383%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Printing%20from%20a%20variable%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2757383%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1157733%22%20target%3D%22_blank%22%3E%40Jonathan865%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20ExportPages()%0A%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Set%20wsh%20%3D%20Worksheets(%22Certificate%20Template%20-%20AD%22)%0A%20%20%20%20n%20%3D%20wsh.PageSetup.Pages.Count%0A%20%20%20%20For%20i%20%3D%201%20To%20n%0A%20%20%20%20%20%20%20%20wsh.ExportAsFixedFormat%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20Type%3A%3DxlTypePDF%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20Filename%3A%3Dwsh.Range(%22B%22%20%26amp%3B%2056%20*%20(i%20-%201)%20%2B%201).Value%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20From%3A%3Di%2C%20To%3A%3Di%0A%20%20%20%20Next%20i%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

 

1 Reply

@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