How to Print Worksheets only when a trigger cell on worksheet =True

[Randy11]

Have a workbook that consolidates data into preformated reports on several worksheets. Reports are filled with info via formula. In some situations, not all reports are needed. In cell A1 on sheets, Sheet1, Sheet2, Sheet3 & Sheet4 there will be either True or False.

Recording or writing macro to print this sheets is the easy part. What I want is only those to print that have True in A1. Could be all or could be just 1 or 2 etc.

Appreciate any assistance with VB code to acheive desired results.

 

[SkipVought]

hi,

Like...

if Sheet1.cells(1,"A").value then sheet1.printout 1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Perfect, Thank you Skip..... Your expertise appreciated.

Have a good one....

 

[Randy11]

The code above, have attempted to use this again today with no luck. Can you clarify where the code should be located. I.e. in a module, under Worksheet or Workbook? Example would be helpful.

 

[SkipVought]

What event initiates print?

Where is your print code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

A number of possibilities....
Sub PrintCurrent()
'
' ActiveSheetPrint Macro
'

'
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

End Sub

Or something like the following:

If included within a sequence of a Macro with other prior steps.
Sheet30.PrintOut , , 1

or

Sub Print_Adj()
'
' Print_Adj Macro
'

'
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

 

[SkipVought]

So do you have a BUTTON that kicks of a Print process?

I gave you an example for deciding whether to print a sheet or not. Hear's how to loop thru all sheets.

dim ws as worksheet

for each ws in thisworkbook.worksheets
  with ws
    if .cells(1,"A").value then .printout 1  
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

I like this, how would I apply this to only 5 worksheets.
So say I want to by button click print the following sheets, if the Cell A1 has a value in it?
Sheet1, Sheet2, Consolidate, Rerun, & Disposals

 

[SkipVought]

dim ws as worksheet

for each ws in thisworkbook.worksheets
  with ws
    select case .name
      case "Sheet1", "Sheet2", "Consolidate", "Rerun", "Disposals"
        if .cells(1,"A").value then .printout 1  
    end select
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Have tried this to no avail. Additional Ideas appreciated



Sub Print_or_Not()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
With ws
If .Cells(1, "A").Value Then .PrintOut 1
End With
Next

End Sub

 

[SkipVought]

to no avail of WHAT?

Please be specific when you say something is not working. This wastes your time and mine.

EXACTLY what happens?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

This code was tested. Nothing Prints at all. Have a value in Cell A1 on 3 of these sheets. This line is highlighted in Yellow "If .Cells(1, "A").Value Then .PrintOut 1


Sub Print_or_Not()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
With ws
Select Case .Name
Case "Sheet1", "Sheet2", "Consolidate", "Rerun", "Disposals"
If .Cells(1, "A").Value Then .PrintOut 1
End Select
End With
Next


End Sub

 

[SkipVought]

So do you have any TRUE, not "TRUE", in A1 on ANY of your sheets? Which sheet(s)?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Your code WORKS AS POSTED, as I again tested it.

If you wish, attach your workbook to an eMail to
[tt]
ii36250

at

bh

dot

com
[/tt]
and then notify me here.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Hi Skip This solution did work perfectly. My apologies, had other code that was conflicting with this print code.
Thank you for your great help & patience...

 

[SkipVought]

Wonderful! Glad to help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}