Does anyone know if there is a way to print certain worksheets in a workbook
based on values in a cell? I'm wanting to print all customers that are
currently active on the status worksheet. Each Customer has there own
worksheet. I would like to print their worksheet based on if they are marked
"active" on the status worksheet. Any ideas, please feel free to tell me.
--
Thank you,
Thomas Vanderhoof
Noble Phone Services, Inc.

Hi Thomas

One way

With in A the sheet names and in B active (on the status sheet) or not try this

Sub test()
Dim cell As Range
For Each cell In Sheets("status").Columns("A").Cells.SpecialCells(x lCellTypeConstants)
If LCase(cell.Offset(0, 1).Value) = "active" Then Sheets(cell.Value).PrintOut
Next cell
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Thomas" wrote in message ...
Does anyone know if there is a way to print certain worksheets in a workbook
based on values in a cell? I'm wanting to print all customers that are
currently active on the status worksheet. Each Customer has there own
worksheet. I would like to print their worksheet based on if they are marked
"active" on the status worksheet. Any ideas, please feel free to tell me.
--
Thank you,
Thomas Vanderhoof
Noble Phone Services, Inc.

Something like the below (untested)

change MasterSheet to name of master sheet
change Range("A1:A25") to the appropriate range containing Active
cel.Offset(0,1) means the cell to the immediate right of the cell. change
the 1 to the correct number.
Once you get it working - replace PrintPreview with PrintOut

====================================
Dim wksh As String, cel As Range

For Each cel In Sheets("MasterSheet").Range("A1:A25")
If LCase(cel) = "active" Then
wksh = cel.Offset(0, 1)
Worksheets(wksh).PrintPreview
End If
Next
=========================
--
steveB

Remove "AYN" from email to respond
"Thomas" wrote in message
...
Does anyone know if there is a way to print certain worksheets in a
workbook
based on values in a cell? I'm wanting to print all customers that are
currently active on the status worksheet. Each Customer has there own
worksheet. I would like to print their worksheet based on if they are
marked
"active" on the status worksheet. Any ideas, please feel free to tell me.
--
Thank you,
Thomas Vanderhoof
Noble Phone Services, Inc.

thank you for your help, but how does it know which worksheet to print with
the command line:

Worksheets(wksh).PrintPreview

Do I put all the worksheet names within this like the following:

Worksheets(wksh1, wksh2, wksh3).PrintPreview

Thank you for your help,
Thomas Vanderhoof
"STEVE BELL" wrote:

Something like the below (untested)

change MasterSheet to name of master sheet
change Range("A1:A25") to the appropriate range containing Active
cel.Offset(0,1) means the cell to the immediate right of the cell. change
the 1 to the correct number.
Once you get it working - replace PrintPreview with PrintOut

====================================
Dim wksh As String, cel As Range

For Each cel In Sheets("MasterSheet").Range("A1:A25")
If LCase(cel) = "active" Then
wksh = cel.Offset(0, 1)
Worksheets(wksh).PrintPreview
End If
Next
=========================
--
steveB

Remove "AYN" from email to respond
"Thomas" wrote in message
...
Does anyone know if there is a way to print certain worksheets in a
workbook
based on values in a cell? I'm wanting to print all customers that are
currently active on the status worksheet. Each Customer has there own
worksheet. I would like to print their worksheet based on if they are
marked
"active" on the status worksheet. Any ideas, please feel free to tell me.
--
Thank you,
Thomas Vanderhoof
Noble Phone Services, Inc.

Tom,

You can loop through all the worksheets or loop through a list of worksheets

Dim wksh as Worksheet

For each wksh in thisworkbook.worksheets
wksh.PrintPreview
next

or

with worksheets listed on Sheet5 Range(A1:A5)

Dim wksh as String, x as Integer

For x = 1 to 5
wksh = Sheets("Sheet5").Cells(x,1).Text
Worksheets(wksh).PrintPreview
Next

or if you know how to build an array - than you can make your
idea work Worksheets(wksh1, wksh2, wksh3)
(unfortunately arrays are not my thing - they confuse me)

Another way to create a loop is either have a list on a worksheet and
an adjacent cell where you place a recognizable character as a check mark.
(x, 1, y, or .....) Than have the loop look for the character.

Or build a form to do this...

--
steveB

Remove "AYN" from email to respond
"Tom" wrote in message
...
thank you for your help, but how does it know which worksheet to print
with
the command line:

Worksheets(wksh).PrintPreview

Do I put all the worksheet names within this like the following:

Worksheets(wksh1, wksh2, wksh3).PrintPreview

Thank you for your help,
Thomas Vanderhoof
"STEVE BELL" wrote:

Something like the below (untested)

change MasterSheet to name of master sheet
change Range("A1:A25") to the appropriate range containing Active
cel.Offset(0,1) means the cell to the immediate right of the cell.
change
the 1 to the correct number.
Once you get it working - replace PrintPreview with PrintOut

====================================
Dim wksh As String, cel As Range

For Each cel In Sheets("MasterSheet").Range("A1:A25")
If LCase(cel) = "active" Then
wksh = cel.Offset(0, 1)
Worksheets(wksh).PrintPreview
End If
Next
=========================
--
steveB

Remove "AYN" from email to respond
"Thomas" wrote in message
...
Does anyone know if there is a way to print certain worksheets in a
workbook
based on values in a cell? I'm wanting to print all customers that are
currently active on the status worksheet. Each Customer has there own
worksheet. I would like to print their worksheet based on if they are
marked
"active" on the status worksheet. Any ideas, please feel free to tell
me.
--
Thank you,
Thomas Vanderhoof
Noble Phone Services, Inc.

Hi Steve,
Thanks for your help. I took some from you, and some from another. I then
made this:

Sub PrintBills()

Dim curCell As String, wksh As String, x As Integer
x = 0

For Counter = 1 To 20
x = x + 1


wksh = Sheets("Bill Array").Cells(x, 1).Text

curCell = Worksheets("StatusReport").Cells(Counter, 2)

If LCase(curCell) = "pending" Then Worksheets(wksh).PrintOut


Next Counter



End Sub


This works perfectly for what I want it to do. I'll just modify the for
statement. Fist time working with Visual Basic, but I find it very useful and
easy to pick up.
Thank you,
Thomas

"STEVE BELL" wrote:

Tom,

You can loop through all the worksheets or loop through a list of worksheets

Dim wksh as Worksheet

For each wksh in thisworkbook.worksheets
wksh.PrintPreview
next

or

with worksheets listed on Sheet5 Range(A1:A5)

Dim wksh as String, x as Integer

For x = 1 to 5
wksh = Sheets("Sheet5").Cells(x,1).Text
Worksheets(wksh).PrintPreview
Next

or if you know how to build an array - than you can make your
idea work Worksheets(wksh1, wksh2, wksh3)
(unfortunately arrays are not my thing - they confuse me)

Another way to create a loop is either have a list on a worksheet and
an adjacent cell where you place a recognizable character as a check mark.
(x, 1, y, or .....) Than have the loop look for the character.

Or build a form to do this...

--
steveB

Remove "AYN" from email to respond
"Tom" wrote in message
...
thank you for your help, but how does it know which worksheet to print
with
the command line:

Worksheets(wksh).PrintPreview

Do I put all the worksheet names within this like the following:

Worksheets(wksh1, wksh2, wksh3).PrintPreview

Thank you for your help,
Thomas Vanderhoof
"STEVE BELL" wrote:

Something like the below (untested)

change MasterSheet to name of master sheet
change Range("A1:A25") to the appropriate range containing Active
cel.Offset(0,1) means the cell to the immediate right of the cell.
change
the 1 to the correct number.
Once you get it working - replace PrintPreview with PrintOut

====================================
Dim wksh As String, cel As Range

For Each cel In Sheets("MasterSheet").Range("A1:A25")
If LCase(cel) = "active" Then
wksh = cel.Offset(0, 1)
Worksheets(wksh).PrintPreview
End If
Next
=========================
--
steveB

Remove "AYN" from email to respond
"Thomas" wrote in message
...
Does anyone know if there is a way to print certain worksheets in a
workbook
based on values in a cell? I'm wanting to print all customers that are
currently active on the status worksheet. Each Customer has there own
worksheet. I would like to print their worksheet based on if they are
marked
"active" on the status worksheet. Any ideas, please feel free to tell
me.
--
Thank you,
Thomas Vanderhoof
Noble Phone Services, Inc.

Thomas,

You are more than welcome!

Thanks for keeping me informed of your success.

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"thomas" wrote in message
...
Hi Steve,
Thanks for your help. I took some from you, and some from another. I then
made this:

Sub PrintBills()

Dim curCell As String, wksh As String, x As Integer
x = 0

For Counter = 1 To 20
x = x + 1


wksh = Sheets("Bill Array").Cells(x, 1).Text

curCell = Worksheets("StatusReport").Cells(Counter, 2)

If LCase(curCell) = "pending" Then Worksheets(wksh).PrintOut


Next Counter



End Sub


This works perfectly for what I want it to do. I'll just modify the for
statement. Fist time working with Visual Basic, but I find it very useful
and
easy to pick up.
Thank you,
Thomas

"STEVE BELL" wrote:

Tom,

You can loop through all the worksheets or loop through a list of
worksheets

Dim wksh as Worksheet

For each wksh in thisworkbook.worksheets
wksh.PrintPreview
next

or

with worksheets listed on Sheet5 Range(A1:A5)

Dim wksh as String, x as Integer

For x = 1 to 5
wksh = Sheets("Sheet5").Cells(x,1).Text
Worksheets(wksh).PrintPreview
Next

or if you know how to build an array - than you can make your
idea work Worksheets(wksh1, wksh2, wksh3)
(unfortunately arrays are not my thing - they confuse me)

Another way to create a loop is either have a list on a worksheet and
an adjacent cell where you place a recognizable character as a check
mark.
(x, 1, y, or .....) Than have the loop look for the character.

Or build a form to do this...

--
steveB

Remove "AYN" from email to respond
"Tom" wrote in message
...
thank you for your help, but how does it know which worksheet to print
with
the command line:

Worksheets(wksh).PrintPreview

Do I put all the worksheet names within this like the following:

Worksheets(wksh1, wksh2, wksh3).PrintPreview

Thank you for your help,
Thomas Vanderhoof
"STEVE BELL" wrote:

Something like the below (untested)

change MasterSheet to name of master sheet
change Range("A1:A25") to the appropriate range containing Active
cel.Offset(0,1) means the cell to the immediate right of the cell.
change
the 1 to the correct number.
Once you get it working - replace PrintPreview with PrintOut

====================================
Dim wksh As String, cel As Range

For Each cel In Sheets("MasterSheet").Range("A1:A25")
If LCase(cel) = "active" Then
wksh = cel.Offset(0, 1)
Worksheets(wksh).PrintPreview
End If
Next
=========================
--
steveB

Remove "AYN" from email to respond
"Thomas" wrote in message
...
Does anyone know if there is a way to print certain worksheets in a
workbook
based on values in a cell? I'm wanting to print all customers that
are
currently active on the status worksheet. Each Customer has there
own
worksheet. I would like to print their worksheet based on if they
are
marked
"active" on the status worksheet. Any ideas, please feel free to
tell
me.
--
Thank you,
Thomas Vanderhoof
Noble Phone Services, Inc.