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(xlCellTypeConstants)
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" <Thomas@discussions.microsoft.com> wrote in message news:DED9847A-055B-4D64-A66F-9C11F3AB33B6@microsoft.com...
> 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" <Thomas@discussions.microsoft.com> wrote in message
news:DED9847A-055B-4D64-A66F-9C11F3AB33B6@microsoft.com...
> 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" <Thomas@discussions.microsoft.com> wrote in message
> news:DED9847A-055B-4D64-A66F-9C11F3AB33B6@microsoft.com...
> > 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" <Tom@discussions.microsoft.com> wrote in message
news:FEF9B25C-3744-4CE7-883A-AADCF80A2AD1@microsoft.com...
> 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" <Thomas@discussions.microsoft.com> wrote in message
>> news:DED9847A-055B-4D64-A66F-9C11F3AB33B6@microsoft.com...
>> > 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" <Tom@discussions.microsoft.com> wrote in message
> news:FEF9B25C-3744-4CE7-883A-AADCF80A2AD1@microsoft.com...
> > 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" <Thomas@discussions.microsoft.com> wrote in message
> >> news:DED9847A-055B-4D64-A66F-9C11F3AB33B6@microsoft.com...
> >> > 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" <thomas@discussions.microsoft.com> wrote in message
news:06D38B0F-EE04-4997-A43F-18F0851CACE9@microsoft.com...
> 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" <Tom@discussions.microsoft.com> wrote in message
>> news:FEF9B25C-3744-4CE7-883A-AADCF80A2AD1@microsoft.com...
>> > 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" <Thomas@discussions.microsoft.com> wrote in message
>> >> news:DED9847A-055B-4D64-A66F-9C11F3AB33B6@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks