Print to pdf and use a list for loop

[mark5544]

Hi, I want to print the active sheet to pdf. Active sheet contains value in cell A9, and other cells will use a vlookup based on A9 . I have this macro, it will print the active sheet to pdf and is working fine.


Sub printpdf()
'
' printpdf Macro
'

ChDir ActiveWorkbook.Path & ""
fileSaveName = Worksheets("Master").Range("A9").Value & " pdf file"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fileSaveName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False


End Sub

Now I want to print many pdf files based on different values in A9. I want cell A9 to loop through a list (example below) and for each value print the active sheet.

List:
55
66
77
88

Macro logic:
A9 = 55
print
A9 = 66
print
A9 = 77
print
A9 = 88
print

Can anyone advise how to do this? Many thanks.

 

[rpaulson]

quick loop:

for i = 55 to 88 step 11
[A9] = i
print
next i

hth,
Ross

 

[mark5544]

sorry list is much longer and is just random numbers

 

[rpaulson]

make an array of the number then loop through it.

dim vars as variant, i as long
vars = array(2, 8, 15, 21, 28, 35, 49, 56, 63, 70, 76)
for i = lbound(vars) to ubound(vars)
[A9] =vars(i)
print
next i

 

[mark5544]

make an array of the number then loop through it.

dim vars as variant, i as long
vars = array(2, 8, 15, 21, 28, 35, 49, 56, 63, 70, 76)
for i = lbound(vars) to ubound(vars)
[A9] =vars(i)
print
next i

I cant get it to work 100%. It goes through the values but it doesnt save any file. I want to save a pdf copy in the same folder as the macro file. Any ideas?

Sub printpdf()
'
' printpdf 2900, 3300, 3400, 4100, 4500 Macro
'


    Dim vars As Variant, i As Long
    vars = Array(300, 800, 2700, 2800)
    For i = LBound(vars) To UBound(vars)
    [A9] = vars(i)
    
    ChDir ActiveWorkbook.Path & "\"
    fileSaveName = Worksheets("Master").Range("A9").Value & " marginalstruktur"
    
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fileSaveName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    Next i
    


End Sub

 

[mark5544]

Edit: its working, thank you for your help!

Dim vars As Variant, i As Long
vars = Array(300, 800, 2700, 2800)
For i = LBound(vars) To UBound(vars)
[A9] = vars(i)

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveWorkbook.Path & "" & Worksheets("Master").Range("A9").Value & " marginalstruktur", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Next i

 

[dgarrod]

Bump for redefining for similar need.

I have a "Build Sheet" tab in my workbook, this enables us to make prints of all the details needed for each line item.

TLDR: Can you help me write a macro to call a range/array 'MISC'!S5:S254 or from name managed "BSPrint". Into a macro that then loops to print the "Build Sheet" for each line in this "BSPrint" via cycling this range through the value in J5 of this "Build Sheet" worksheet.

Details:
I have a program that I inherited at my company. We use this program to separate, parse, and determine the details needed for a certain option available on all our orders. This option gets configured by the customer for their individual needs and then we formulate for each line item all the details needed for the factory floor. All these details are displayed on a single sheet in our program. These "Build Sheet" have a drop down input for cycling through the line items on the order. In an attempt to print this automatically, not have to manually click change each line item and then click print, I have parsed out the order line items and removed all blanks where applicable to enable me to use a VBA macro to call the array of printable line items. In this tab we cycle our orders through by line item. I want to use this setup with the array to print only the needed orders details (eg. line item 2, 4, 5, 10, 15, 20).