Print Multiple Sheets with If statements for all sheets

Question

Basically there are 11 worksheets I need printed in a single pdf file. 1st worksheet needs to be fully printed, other 10 need to be based on a condition of a particular cell being >0.

In my case there is a number that is spit out into a particular cell for each worksheet. Half are C16, other half is C20 (And I can't change them all to be the exact same, because other codes are reliant on it too).

Is there a way to create a macro that would print all these worksheets into one pdf file with this condition in place?

Here's a code I tried that doesn't have the if statement included because I don't know where I would implement it. SG is the tab that does NOT require a condition. Because the cells are mixed of which ones need C16 and C20, let's say 1-5 require C15, 6-10 require C20. Thank you!

Sub zxcv()

    Sheets(Array("SG", _
        "sh1", _
        "sh2", _
        "sh3", _
        "sh4", _
        "sh5", _
        "sh6", _
        "sh7", _
        "sh8", _
        "sh9", _
        "sh10")).Select

    Sheets("SG").Activate

    ChDir "C:\Users\mynamehere\Downloads\test"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\mynamehere\Downloads\test\test2.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
End Sub

Declare a variable to hold the array of sheet names. Go through the sheets and add names to the array as you find matches. The rest of your code should work as is (if it already does for the whole list) — chris neilsen, Jul 30 '19 at 19:42
Are the sheets that can have a value in C16 always the same sheet, and the same for C20? — GMalc, Jul 30 '19 at 20:39

score 0 · Answer 1 · answered Jul 30 '19 at 19:48

Something like this should work.

Sub zxcv()

    Dim v, arr, vals

    'sheetName:rangeAddress
    arr = Array("sh1:C16", "sh2:C16", "sh3:C16", "sh4:C16", "sh5:C16", _
                "sh6:C20", "sh7:C20", "sh8:C20", "sh9:C20", "sh10:C20")

    Sheets("SG").Select

    For Each v In arr
        vals = Split(v, ":") 'split to sheet name and range address
        With Sheets(vals(0))
            'Add to current sheet selection?
            'Replace:=False prevents de-selecting any already-selected sheet(s)
            If .Range(vals(1)).Value > 0 Then .Select Replace:=False
        End With
    Next v

    Sheets("SG").Activate

    ChDir "C:\Users\mynamehere\Downloads\test"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\mynamehere\Downloads\test\test2.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
End Sub

Warcupine · Answer 2 · 2019-07-30T19:48:21.007

Don't use select or activate, they are almost never necessary. How to avoid using Select in Excel VBA

That being said, you will first need to separate the sheets with different cell references.

After that do the cell comparison in the same loop and export if the criteria is met.

Sub zxcv()
    dim elements as variant
    dim sheetval as integer
    for each elements in activeworkbook.sheets ' Give this a real workbook reference
        if not elements.name like "SG" then
        sheetval = cint(right(elements.name, len(elements.name) - (instr(1, elements.name, "sh") + 1)) ) 'Get the sheet number
        if sheetval >= 6 then 'Determine which cell you need to look at
            if elements.range("C20").value > 0 then 'Test value
                 elements.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                 "C:\Users\mynamehere\Downloads\test\test2.pdf", _
                 Quality:=xlQualityStandard, _
                 IncludeDocProperties:=True, IgnorePrintAreas:=False, _ 
                 OpenAfterPublish:=True
            end if
        else
            if elements.range("C16").value > 0 then
                 elements.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                 "C:\Users\mynamehere\Downloads\test\test2.pdf", 
                  Quality:=xlQualityStandard, _
                  IncludeDocProperties:=True, IgnorePrintAreas:=False, _ 
                  OpenAfterPublish:=_
                  True
             end if
        end if
        else
           elements.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
           "C:\Users\mynamehere\Downloads\test\test2.pdf", 
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, _ 
            OpenAfterPublish:=_
            True
        end if
    next
end sub

EDIT: Forgot to put an export for the main sheet.

Print Multiple Sheets with If statements for all sheets

2 Answers2