Print based on a cell contents

[EdGilmore]

I have a workbook set up that I would like to run a macro for to print a selection of sheets based on the text input into a cell. The worksheets are labeled:

Details
NFP1
NFP2

There are other sheets in the workbook, but these are not required to be printed.

So, if cell k21 on "details" contains the phrase "NFP1 and NFP2", I want all 3 sheets to print.

If k21 contains "NFP1 and Exit", I want to print "details", "NFP1" and then link to a seperate workbook entitled PLP for which I currently have a seperate macro set up:

Sub Open_exit()
'
' Open_exit Macro
'

'
ChDir "H:\"
Workbooks.Open Filename:="H:\PLP1.xls"
End Sub

If k21 is blank, I want the print function to become defunct.

Please help!

 

[jim may]

This should get you started.... write back if problems,,, (though I'm not going to be available for the rest of the evening...................

Sub Foo()
Dim MyWks
On Error Resume Next
MyWks = Array("Details", "NFP1", "NFP2")
Status = Worksheets("Details").Range("K21").Value
Select Case Status
Case "NFP1 and NFP2"
    Sheets("Details").Activate
    ActiveSheet.PrintPreview
    Sheets("NFP1").Activate
    ActiveSheet.PrintPreview
    Sheets("NFP2").Activate
    ActiveSheet.PrintPreview

Case "NFP1 and Exit"
    Sheets("Details").Activate
    ActiveSheet.PrintPreview
    Sheets("NFP1").Activate
    ActiveSheet.PrintPreview
    Workbooks.Open Filename:="H:\PLP1.xls"
Case ""
    Exit Sub
End Select
End Sub

 

[EdGilmore]

Thanks Jim, really helpful. Just had to tweak the .PrintPreview lines to .PrintOut which worked a treat.

 

[jim may]

Thanks for the feedback

 

[pginther]

I have a similar situation and am trying to make this solution work but I have one minor difference that has me hung up.

Rather than having a single cell reference to take into account, I actually need to evaluate the contents of 6 difference cells in my primary worksheet.

Each of these cells corresponds to another sheet that contains more detailed information related to that cell. I want to print the primary worksheet and the sheet associated with each of the evaluated cells where the cell value is greater than zero. For each evaluated cell that has a zero value, I want to skip printing the associated sheet.

Can you help point me in the right direction?

Thanks!