I have a spreadsheet with about ten different tabs. I am writing a macro
that will print all of these tabs that have data in them. I can write an if
statement asking if the total doesn't equal 0 then print. When I do this
each tab is printed out one at a time. This won't alow the page numbers in
the footer to work and in our office printing these tabs separately can cause
problem at the printer - since many people are using the printer at once.

What I don't know how to write is to "select a workgroup" (select multiple
tabs at once) of tabs that totals don't equal zero.

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a macro
that will print all of these tabs that have data in them. I can write an if
statement asking if the total doesn't equal 0 then print. When I do this
each tab is printed out one at a time. This won't alow the page numbers in
the footer to work and in our office printing these tabs separately can cause
problem at the printer - since many people are using the printer at once.

What I don't know how to write is to "select a workgroup" (select multiple
tabs at once) of tabs that totals don't equal zero.

Thanks, that helps. But I don't want to tell the macro which sheets to
select, I want the marco to figure that out itself. Even the number of sheet
to print will change from job to job. Some could print just one of the ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a macro
that will print all of these tabs that have data in them. I can write an if
statement asking if the total doesn't equal 0 then print. When I do this
each tab is printed out one at a time. This won't alow the page numbers in
the footer to work and in our office printing these tabs separately can cause
problem at the printer - since many people are using the printer at once.

What I don't know how to write is to "select a workgroup" (select multiple
tabs at once) of tabs that totals don't equal zero.

Dawn
Something like this will loop through all the sheets in the workbook. I
put in an IF statement to check some value in each sheet and if the criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets to
select, I want the marco to figure that out itself. Even the number of
sheet
to print will change from job to job. Some could print just one of the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can write
an if
statement asking if the total doesn't equal 0 then print. When I do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.

Yes, this is how I have it now. In the ['Your code here] section I have it
print. I need it to add it to the [Sheets(Array("Sheet1", "Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?


"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the workbook. I
put in an IF statement to check some value in each sheet and if the criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets to
select, I want the marco to figure that out itself. Even the number of
sheet
to print will change from job to job. Some could print just one of the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can write
an if
statement asking if the total doesn't equal 0 then print. When I do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.

Shawn
I don't understand what you want that the code I gave you doesn't do.
With the code I gave you, you can do whatever you want with each sheet in
the workbook. Perhaps you have some sheets that you want to exclude from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't do for
you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?


"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the workbook.
I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets to
select, I want the marco to figure that out itself. Even the number of
sheet
to print will change from job to job. Some could print just one of the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When I do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately
can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.

Shawn
Change those "Or" that I gave you in my last code to "And". Otto
"Otto Moehrbach" wrote in message
...
Shawn
I don't understand what you want that the code I gave you doesn't do.
With the code I gave you, you can do whatever you want with each sheet in
the workbook. Perhaps you have some sheets that you want to exclude from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't do
for you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?


"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the
workbook. I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets
to
select, I want the marco to figure that out itself. Even the number
of
sheet
to print will change from job to job. Some could print just one of
the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When I
do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately
can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.

I understand how to do an If Statement and I will have to wrap it in a Do
Loop. This is a draft of what I have in mind

Do
Select the next tab

If total < 0 then

Add to WorkGroup (This is what I dont understand)

End If

Loop 10 time

Right now I print at the €œAdd to WorkGroup€� and this is causing problems. I
want the end result outside of the above loop to be a WorkGroup of all the
tabs on the spreadsheet that has values in their totals so they can all be
printed at once.

I do appreciate the responses though.

Thanks,

Shawn


"Otto Moehrbach" wrote:

Shawn
Change those "Or" that I gave you in my last code to "And". Otto
"Otto Moehrbach" wrote in message
...
Shawn
I don't understand what you want that the code I gave you doesn't do.
With the code I gave you, you can do whatever you want with each sheet in
the workbook. Perhaps you have some sheets that you want to exclude from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't do
for you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?


"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the
workbook. I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets
to
select, I want the marco to figure that out itself. Even the number
of
sheet
to print will change from job to job. Some could print just one of
the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When I
do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately
can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.

OK I got the code below to work, but it still doesn't do what I want. The
two changes to the right is what I need to work - and it doesn't. The code
to the left would work if I knew how many tabs would be printed, but I don't
which is why I need something simular to the code to the Right.

Sub Macro1()

Dim Sht1
Dim Sht2
Dim Sht3
Dim TheTab
Dim cnt

Sheets("SUMMARY").Select
Application.Goto Reference:="TabNames"
TheTab = ActiveCell.Value

Sheets(TheTab).Select
Range("N111").Select

If ActiveCell.Value < 0 Then

Sht1 = TheTab

End If

Sheets("SUMMARY").Select
ActiveCell.Offset(1, 0).Select
TheTab = ActiveCell.Value

Sheets(TheTab).Select
Range("N111").Select

If ActiveCell.Value < 0 Then

Sht2 = TheTab ' Sht2 = Sht1 + ", " + TheTab

End If

Sheets(Array(Sht1, Sht2)).Select ' Sheets(Array(Sht2)).Select
Sheets(Sht1).Activate

End Sub


"Shawn777" wrote:

I understand how to do an If Statement and I will have to wrap it in a Do
Loop. This is a draft of what I have in mind

Do
Select the next tab

If total < 0 then

Add to WorkGroup (This is what I dont understand)

End If

Loop 10 time

Right now I print at the €œAdd to WorkGroup€� and this is causing problems. I
want the end result outside of the above loop to be a WorkGroup of all the
tabs on the spreadsheet that has values in their totals so they can all be
printed at once.

I do appreciate the responses though.

Thanks,

Shawn


"Otto Moehrbach" wrote:

Shawn
Change those "Or" that I gave you in my last code to "And". Otto
"Otto Moehrbach" wrote in message
...
Shawn
I don't understand what you want that the code I gave you doesn't do.
With the code I gave you, you can do whatever you want with each sheet in
the workbook. Perhaps you have some sheets that you want to exclude from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't do
for you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?


"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the
workbook. I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets
to
select, I want the marco to figure that out itself. Even the number
of
sheet
to print will change from job to job. Some could print just one of
the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When I
do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately
can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.

Shawn
Look at this macro. Maybe this is what you want. This macro loops
through all the sheets, checks for A1<0 and if the criteria is met, it adds
that sheet name to an array. After the looping is done, it selects the
array (as a group). Post back if you need more. Otto
Sub SetupSheetArray()
Dim ws As Worksheet
Dim ShtArray() As String 'The array you want to set up
Dim c As Long
ReDim ShtArray(1 To 1000)
c = 1
For Each ws In ActiveWorkbook.Worksheets
With ws
If .Range("A1") < 0 Then
ShtArray(c) = ws.Name
c = c + 1
End If
End With
Next ws
ReDim Preserve ShtArray(1 To c)
Sheets(ShtArray()).Select
End Sub
"Shawn777" wrote in message
...
I understand how to do an If Statement and I will have to wrap it in a Do
Loop. This is a draft of what I have in mind

Do
Select the next tab

If total < 0 then

Add to WorkGroup (This is what I don't understand)

End If

Loop 10 time

Right now I print at the "Add to WorkGroup" and this is causing problems.
I
want the end result outside of the above loop to be a WorkGroup of all the
tabs on the spreadsheet that has values in their totals so they can all be
printed at once.

I do appreciate the responses though.

Thanks,

Shawn


"Otto Moehrbach" wrote:

Shawn
Change those "Or" that I gave you in my last code to "And". Otto
"Otto Moehrbach" wrote in message
...
Shawn
I don't understand what you want that the code I gave you doesn't
do.
With the code I gave you, you can do whatever you want with each sheet
in
the workbook. Perhaps you have some sheets that you want to exclude
from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't
do
for you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I
have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at
once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?


"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the
workbook. I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which
sheets
to
select, I want the marco to figure that out itself. Even the
number
of
sheet
to print will change from job to job. Some could print just one of
the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am
writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When
I
do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs
separately
can
cause
problem at the printer - since many people are using the printer
at
once.

What I don't know how to write is to "select a workgroup"
(select
multiple
tabs at once) of tabs that totals don't equal zero.

Shawn,

Try it this way:

Sub test()
Dim ws As Object, i As Long, arr()
i = 0
For Each ws In Sheets
If ws.Range("N111") 0 Then
ReDim Preserve arr(0 To i)
arr(i) = ws.Name
i = i + 1
End If
Next
If Sheets.Count 1 Then
Sheets(arr).Select
End If
End Sub

--
Dan

On Dec 19, 8:58 am, Shawn777
wrote:
OK I got the code below to work, but it still doesn't do what I want. The
two changes to the right is what I need to work - and it doesn't. The code
to the left would work if I knew how many tabs would be printed, but I don't
which is why I need something simular to the code to the Right.

Sub Macro1()

Dim Sht1
Dim Sht2
Dim Sht3
Dim TheTab
Dim cnt

Sheets("SUMMARY").Select
Application.Goto Reference:="TabNames"
TheTab = ActiveCell.Value

Sheets(TheTab).Select
Range("N111").Select

If ActiveCell.Value < 0 Then

Sht1 = TheTab

End If

Sheets("SUMMARY").Select
ActiveCell.Offset(1, 0).Select
TheTab = ActiveCell.Value

Sheets(TheTab).Select
Range("N111").Select

If ActiveCell.Value < 0 Then

Sht2 = TheTab ' Sht2 = Sht1 + ", " + TheTab

End If

Sheets(Array(Sht1, Sht2)).Select ' Sheets(Array(Sht2)).Select
Sheets(Sht1).Activate

End Sub



"Shawn777" wrote:
I understand how to do an If Statement and I will have to wrap it in a Do
Loop. This is a draft of what I have in mind

Do
Select the next tab

If total < 0 then

Add to WorkGroup (This is what I don't understand)

End If

Loop 10 time

Right now I print at the "Add to WorkGroup" and this is causing problems. I
want the end result outside of the above loop to be a WorkGroup of all the
tabs on the spreadsheet that has values in their totals so they can all be
printed at once.

I do appreciate the responses though.

Thanks,

Shawn

"Otto Moehrbach" wrote:

Shawn
Change those "Or" that I gave you in my last code to "And". Otto
"Otto Moehrbach" wrote in message
...
Shawn
I don't understand what you want that the code I gave you doesn't do.
With the code I gave you, you can do whatever you want with each sheet in
the workbook. Perhaps you have some sheets that you want to exclude from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't do
for you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?

"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the
workbook. I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets
to
select, I want the marco to figure that out itself. Even the number
of
sheet
to print will change from job to job. Some could print just one of
the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761

"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When I
do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately
can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.- Hide quoted text -

- Show quoted text -

Awesome, that is way above my ability €“ I would have never figured that out.
Thank You ALL

Shawn


"Otto Moehrbach" wrote:

Shawn
Look at this macro. Maybe this is what you want. This macro loops
through all the sheets, checks for A1<0 and if the criteria is met, it adds
that sheet name to an array. After the looping is done, it selects the
array (as a group). Post back if you need more. Otto
Sub SetupSheetArray()
Dim ws As Worksheet
Dim ShtArray() As String 'The array you want to set up
Dim c As Long
ReDim ShtArray(1 To 1000)
c = 1
For Each ws In ActiveWorkbook.Worksheets
With ws
If .Range("A1") < 0 Then
ShtArray(c) = ws.Name
c = c + 1
End If
End With
Next ws
ReDim Preserve ShtArray(1 To c)
Sheets(ShtArray()).Select
End Sub
"Shawn777" wrote in message
...
I understand how to do an If Statement and I will have to wrap it in a Do
Loop. This is a draft of what I have in mind

Do
Select the next tab

If total < 0 then

Add to WorkGroup (This is what I don't understand)

End If

Loop 10 time

Right now I print at the "Add to WorkGroup" and this is causing problems.
I
want the end result outside of the above loop to be a WorkGroup of all the
tabs on the spreadsheet that has values in their totals so they can all be
printed at once.

I do appreciate the responses though.

Thanks,

Shawn


"Otto Moehrbach" wrote:

Shawn
Change those "Or" that I gave you in my last code to "And". Otto
"Otto Moehrbach" wrote in message
...
Shawn
I don't understand what you want that the code I gave you doesn't
do.
With the code I gave you, you can do whatever you want with each sheet
in
the workbook. Perhaps you have some sheets that you want to exclude
from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't
do
for you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I
have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at
once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?


"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the
workbook. I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which
sheets
to
select, I want the marco to figure that out itself. Even the
number
of
sheet
to print will change from job to job. Some could print just one of
the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761


"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am
writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When
I
do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs
separately
can
cause
problem at the printer - since many people are using the printer
at
once.

What I don't know how to write is to "select a workgroup"
(select
multiple
tabs at once) of tabs that totals don't equal zero.

Awesome, that is way above my ability €“ I would have never figured that out.
Thank You ALL

Shawn


"Dan R." wrote:

Shawn,

Try it this way:

Sub test()
Dim ws As Object, i As Long, arr()
i = 0
For Each ws In Sheets
If ws.Range("N111") 0 Then
ReDim Preserve arr(0 To i)
arr(i) = ws.Name
i = i + 1
End If
Next
If Sheets.Count 1 Then
Sheets(arr).Select
End If
End Sub

--
Dan

On Dec 19, 8:58 am, Shawn777
wrote:
OK I got the code below to work, but it still doesn't do what I want. The
two changes to the right is what I need to work - and it doesn't. The code
to the left would work if I knew how many tabs would be printed, but I don't
which is why I need something simular to the code to the Right.

Sub Macro1()

Dim Sht1
Dim Sht2
Dim Sht3
Dim TheTab
Dim cnt

Sheets("SUMMARY").Select
Application.Goto Reference:="TabNames"
TheTab = ActiveCell.Value

Sheets(TheTab).Select
Range("N111").Select

If ActiveCell.Value < 0 Then

Sht1 = TheTab

End If

Sheets("SUMMARY").Select
ActiveCell.Offset(1, 0).Select
TheTab = ActiveCell.Value

Sheets(TheTab).Select
Range("N111").Select

If ActiveCell.Value < 0 Then

Sht2 = TheTab ' Sht2 = Sht1 + ", " + TheTab

End If

Sheets(Array(Sht1, Sht2)).Select ' Sheets(Array(Sht2)).Select
Sheets(Sht1).Activate

End Sub



"Shawn777" wrote:
I understand how to do an If Statement and I will have to wrap it in a Do
Loop. This is a draft of what I have in mind

Do
Select the next tab

If total < 0 then

Add to WorkGroup (This is what I don't understand)

End If

Loop 10 time

Right now I print at the "Add to WorkGroup" and this is causing problems. I
want the end result outside of the above loop to be a WorkGroup of all the
tabs on the spreadsheet that has values in their totals so they can all be
printed at once.

I do appreciate the responses though.

Thanks,

Shawn

"Otto Moehrbach" wrote:

Shawn
Change those "Or" that I gave you in my last code to "And". Otto
"Otto Moehrbach" wrote in message
...
Shawn
I don't understand what you want that the code I gave you doesn't do.
With the code I gave you, you can do whatever you want with each sheet in
the workbook. Perhaps you have some sheets that you want to exclude from
the "Your code here" part. If that's the case, you can easily exclude
individual sheets with an IF statement like:
If ws.Name<"This" Or
ws.Name<"That" Or
ws.Name<"The Other" Then
'Your code here"
End If
Post back with more detail about what you want that this code doesn't do
for you. HTH Otto
"Shawn777" wrote in message
...
Yes, this is how I have it now. In the ['Your code here] section I have
it
print. I need it to add it to the [Sheets(Array("Sheet1",
"Sheet2")).Select]
in Gary's Student's reply. Then I can print the whole workbook at once -
which is what I want done.

So how do I combine Otto's and Gary's Student's help?

"Otto Moehrbach" wrote:

Dawn
Something like this will loop through all the sheets in the
workbook. I
put in an IF statement to check some value in each sheet and if the
criteria
is met, your code will run. Post back if you need more. HTH Otto
Sub DoEachSht()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C10").Value 10 Then
'Your code here
End If
Next ws
End Sub
"Shawn777" wrote in message
...
Thanks, that helps. But I don't want to tell the macro which sheets
to
select, I want the marco to figure that out itself. Even the number
of
sheet
to print will change from job to job. Some could print just one of
the
ten
and others will print all ten. So can I get one name to equal
("Sheet1,","Sheet2")? Or is there a better way of doing this?

"Gary''s Student" wrote:

You can select several sheets at once with something like:

Sheets(Array("Sheet1", "Sheet2")).Select
--
Gary''s Student - gsnu200761

"Shawn777" wrote:

I have a spreadsheet with about ten different tabs. I am writing a
macro
that will print all of these tabs that have data in them. I can
write
an if
statement asking if the total doesn't equal 0 then print. When I
do
this
each tab is printed out one at a time. This won't alow the page
numbers in
the footer to work and in our office printing these tabs separately
can
cause
problem at the printer - since many people are using the printer at
once.

What I don't know how to write is to "select a workgroup" (select
multiple
tabs at once) of tabs that totals don't equal zero.- Hide quoted text -

- Show quoted text -