macro to print sheets from wrkbk only if data has been typed in
 

I have a workbook that has 24 tabs on it. It is used to evaluate people. An
evaluator will evaluate up to 24 people. But not the max all the time. If
thhey only do say 10, then I only want to print 10 from that workbook. The I
might open 16 workbooks on an evaluation day and need to print only the
worksheeets that have been typed onto. Is there a way to do an if command in
a macro?

macro to print sheets from wrkbk only if data has been typed in
 

Certainly it's possible. The key would be to determine one (or more) cell
addresses that would be empty unless the sheet has been used to record an
evaluation. The code below presumes 1 cell (A1) would meet that requirement.
You can change that if required. To put the code into a workbook, open the
workbook, press [Alt]+[F11] and then, from the Visual Basic Editor menu,
choose Insert and Module and copy and paste (and modify) the code into it.
Run the macro from Tools | Macro | Macros. This code prints each sheet
individually, so they would all be numbered page 1 if you have page numbering
set up in the header or footer.

Sub PrintUsedSheets()
Const keyCell = "A1" 'change to must be used cell address
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
If Not IsEmpty(anySheet.Range(keyCell)) Then
anySheet.PrintOut copies:=1
End If
Next
End Sub


"Brian" wrote:

I have a workbook that has 24 tabs on it. It is used to evaluate people. An
evaluator will evaluate up to 24 people. But not the max all the time. If
thhey only do say 10, then I only want to print 10 from that workbook. The I
might open 16 workbooks on an evaluation day and need to print only the
worksheeets that have been typed onto. Is there a way to do an if command in
a macro?

macro to print sheets from wrkbk only if data has been typed i
 

That hit the nail on the head. With what I do at work I would pull my hair
out if not for this website. Thanks for all you do MVP'ers

"JLatham" wrote:

Certainly it's possible. The key would be to determine one (or more) cell
addresses that would be empty unless the sheet has been used to record an
evaluation. The code below presumes 1 cell (A1) would meet that requirement.
You can change that if required. To put the code into a workbook, open the
workbook, press [Alt]+[F11] and then, from the Visual Basic Editor menu,
choose Insert and Module and copy and paste (and modify) the code into it.
Run the macro from Tools | Macro | Macros. This code prints each sheet
individually, so they would all be numbered page 1 if you have page numbering
set up in the header or footer.

Sub PrintUsedSheets()
Const keyCell = "A1" 'change to must be used cell address
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
If Not IsEmpty(anySheet.Range(keyCell)) Then
anySheet.PrintOut copies:=1
End If
Next
End Sub


"Brian" wrote:

I have a workbook that has 24 tabs on it. It is used to evaluate people. An
evaluator will evaluate up to 24 people. But not the max all the time. If
thhey only do say 10, then I only want to print 10 from that workbook. The I
might open 16 workbooks on an evaluation day and need to print only the
worksheeets that have been typed onto. Is there a way to do an if command in
a macro?

macro to print sheets from wrkbk only if data has been typed i
 

You're welcome, and thanks for the Thanks. It's nice to get that.

"Brian" wrote:

That hit the nail on the head. With what I do at work I would pull my hair
out if not for this website. Thanks for all you do MVP'ers

"JLatham" wrote:

Certainly it's possible. The key would be to determine one (or more) cell
addresses that would be empty unless the sheet has been used to record an
evaluation. The code below presumes 1 cell (A1) would meet that requirement.
You can change that if required. To put the code into a workbook, open the
workbook, press [Alt]+[F11] and then, from the Visual Basic Editor menu,
choose Insert and Module and copy and paste (and modify) the code into it.
Run the macro from Tools | Macro | Macros. This code prints each sheet
individually, so they would all be numbered page 1 if you have page numbering
set up in the header or footer.

Sub PrintUsedSheets()
Const keyCell = "A1" 'change to must be used cell address
Dim anySheet As Worksheet
For Each anySheet In ThisWorkbook.Worksheets
If Not IsEmpty(anySheet.Range(keyCell)) Then
anySheet.PrintOut copies:=1
End If
Next
End Sub


"Brian" wrote:

I have a workbook that has 24 tabs on it. It is used to evaluate people. An
evaluator will evaluate up to 24 people. But not the max all the time. If
thhey only do say 10, then I only want to print 10 from that workbook. The I
might open 16 workbooks on an evaluation day and need to print only the
worksheeets that have been typed onto. Is there a way to do an if command in
a macro?