Selecting Sheets based on Cell Criteria.
 

I would like to print the worksheets in a workbook based upon whether the
user has entered data on that sheet. So in a 5 page workbook, depending on
the data entered, the workbook will print (selected worksheets based on this
criteria) pages 1,2,5 or 1,3,4,5 etc. Excel will print selected sheets, I
just need to have these sheets selected based upon cell entry(data present on
a particular sheet). I looked thru the formulas, and considered a macro, but
I couldn't find a criteria to base the macro on.
Any ideas?

Thanks in advance to all.

Selecting Sheets based on Cell Criteria.
 

I am not too sure how sophisticated you are in the world of VBA so I will
start off very general. In the ThisWorkBook Object there is an On_Change
Event. You could capture this and use that to load a collection of
worksheets. Then you just need to print out the collection. Get back to me
with any specifics.
--
HTH...

Jim Thomlinson


"Tim Richards" wrote:

I would like to print the worksheets in a workbook based upon whether the
user has entered data on that sheet. So in a 5 page workbook, depending on
the data entered, the workbook will print (selected worksheets based on this
criteria) pages 1,2,5 or 1,3,4,5 etc. Excel will print selected sheets, I
just need to have these sheets selected based upon cell entry(data present on
a particular sheet). I looked thru the formulas, and considered a macro, but
I couldn't find a criteria to base the macro on.
Any ideas?

Thanks in advance to all.

Selecting Sheets based on Cell Criteria.
 

Something like this.

Option Explicit

Public PrintSheets As New Collection

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
PrintSheets.Add Sh, Sh.Name
On Error GoTo 0

End Sub

Public Sub PrintCollection()
Dim wks As Worksheet

For Each wks In PrintSheets
MsgBox wks.Name 'wks.printout
Next wks
End Sub

--
HTH...

Jim Thomlinson


"Tim Richards" wrote:

I would like to print the worksheets in a workbook based upon whether the
user has entered data on that sheet. So in a 5 page workbook, depending on
the data entered, the workbook will print (selected worksheets based on this
criteria) pages 1,2,5 or 1,3,4,5 etc. Excel will print selected sheets, I
just need to have these sheets selected based upon cell entry(data present on
a particular sheet). I looked thru the formulas, and considered a macro, but
I couldn't find a criteria to base the macro on.
Any ideas?

Thanks in advance to all.

Selecting Sheets based on Cell Criteria.
 

Jim,

Thanks for all your help. I can't act upon this until next week, as I will
be traveling. I will try out your response when I get back. As for my
level.... I'm going for the "Total Immersion" approach. Nobody here is
willing to try... so I'm jumping in with both feet!(let's hope there's no
gurgling sounds). I'm looking for a good reference book, any favorites?

Thanks again,
Tim

"Jim Thomlinson" wrote:

Something like this.

Option Explicit

Public PrintSheets As New Collection

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
PrintSheets.Add Sh, Sh.Name
On Error GoTo 0

End Sub

Public Sub PrintCollection()
Dim wks As Worksheet

For Each wks In PrintSheets
MsgBox wks.Name 'wks.printout
Next wks
End Sub

--
HTH...

Jim Thomlinson


"Tim Richards" wrote:

I would like to print the worksheets in a workbook based upon whether the
user has entered data on that sheet. So in a 5 page workbook, depending on
the data entered, the workbook will print (selected worksheets based on this
criteria) pages 1,2,5 or 1,3,4,5 etc. Excel will print selected sheets, I
just need to have these sheets selected based upon cell entry(data present on
a particular sheet). I looked thru the formulas, and considered a macro, but
I couldn't find a criteria to base the macro on.
Any ideas?

Thanks in advance to all.