Use range to determine which workbooks to print together with .PrintOut

[Lightkeepr]

I have nine worksheets in a file as follows:
SETUP (never printed out)
DATA (never printed out)
POT (never printed out)
PCR
PCOST
JSUM
PRA
DELTA
CSR

On sheet DATA in cells F174:K174 i have formulas that if the worksheet is to be printed the cell will have the sheets name visible. Using VBA, how would I get the sheet names that are visible in that cell range (ignoring the blank or "" cells) and pass them to the .PrintOut as the sheets to be printed together as one document?

 

[richh]

Hi Lightkeeper,

I hope my assumption is correct that the cells are not being formatted to be "invisible" using the custom format ";;;" and are rather some type of IF statement formula displays the name of the sheet or a blank, depending on a condition set, such as =IF(F2=1, "Sheet1", "")

Try this out:

Sub printSheets()
    Dim ws      As Worksheet
    Dim printWS As Worksheet
    
    Set ws = Application.ThisWorkbook.Worksheets("DATA")
    
    For i = 6 To 11
        If ws.Cells(174, i) <> "" Then
            Set printWS = Application.ThisWorkbook.Worksheets(ws.Cells(174, i).Value)
            printWS.PrintOut
        End If
    Next i
End Sub

 

[Lightkeepr]

I was using range to tell the VBA where the cells on row 174 were and your solution did it! Thank you for the help, I appreciate it!

 

[richh]

Any time; happy to help.

 

[Lightkeepr]

Hi Lightkeeper,

I hope my assumption is correct that the cells are not being formatted to be "invisible" using the custom format ";;;" and are rather some type of IF statement formula displays the name of the sheet or a blank, depending on a condition set, such as =IF(F2=1, "Sheet1", "")

Try this out:

Sub printSheets()
    Dim ws      As Worksheet
    Dim printWS As Worksheet
   
    Set ws = Application.ThisWorkbook.Worksheets("DATA")
   
    For i = 6 To 11
        If ws.Cells(174, i) <> "" Then
            Set printWS = Application.ThisWorkbook.Worksheets(ws.Cells(174, i).Value)
            printWS.PrintOut
        End If
    Next i
End Sub

Is there a way with this setup to have them print out as one document instead of all individuals?

 

[richh]

You can use something like:

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintOut, I think. You'd just have to develop a bit of code to determine which sheets need to go into the array.