I need a print macro that only prints worksheets with data on it
Basically, I need excel to print each and every worksheet where there'
a "grand total" value. This value is located in the same cell on ever
worksheet

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

for each sh in activeworkbook
if not isempty(sh.Range("B30")) then
sh.printout
end if
Next

--
regards,
Tom Ogilvy

hailnorm wrote in message
...

I need a print macro that only prints worksheets with data on it.
Basically, I need excel to print each and every worksheet where there's
a "grand total" value. This value is located in the same cell on every
worksheet.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

From you suggestion I've create a WB with Sheet1-Sheet3
And in only Sheet1 Cell B5 I have entered "444".
Grouping All Sheets before running the below I Get an error:
"Circular Dependencies between Modules"
with For Each Sh In ActiveWorkbook highlighted

Sub PrintOnlyCertainSheets()
For Each Sh In ActiveWorkbook
If Not IsEmpty(Sh.Range("B5")) Then
Sh.PrintPreview
End If
Next
End Sub

What have I done wrong?
TIA,,
JMay

"Tom Ogilvy" wrote in message
...
for each sh in activeworkbook
if not isempty(sh.Range("B30")) then
sh.printout
end if
Next

--
regards,
Tom Ogilvy

hailnorm wrote in message
...

I need a print macro that only prints worksheets with data on it.
Basically, I need excel to print each and every worksheet where there's
a "grand total" value. This value is located in the same cell on every
worksheet.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

There was a typo on my part

Sub PrintOnlyCertainSheets()
Dim Sh as Worksheet
For Each Sh In ActiveWorkbook.worksheets
If Not IsEmpty(Sh.Range("B5")) Then
Sh.PrintPreview
End If
Next
End Sub

there is no reason to group sheets unless you want to work with a subset of
all worksheets

Dim sh as Object
for each sh in ActiveWindow.SelectedSheets


--
Regards,
Tom Ogilvy


JMay wrote in message
news:0WtAb.47637$yM6.32904@lakeread06...
From you suggestion I've create a WB with Sheet1-Sheet3
And in only Sheet1 Cell B5 I have entered "444".
Grouping All Sheets before running the below I Get an error:
"Circular Dependencies between Modules"
with For Each Sh In ActiveWorkbook highlighted

Sub PrintOnlyCertainSheets()
For Each Sh In ActiveWorkbook
If Not IsEmpty(Sh.Range("B5")) Then
Sh.PrintPreview
End If
Next
End Sub

What have I done wrong?
TIA,,
JMay

"Tom Ogilvy" wrote in message
...
for each sh in activeworkbook
if not isempty(sh.Range("B30")) then
sh.printout
end if
Next

--
regards,
Tom Ogilvy

hailnorm wrote in message
...

I need a print macro that only prints worksheets with data on it.
Basically, I need excel to print each and every worksheet where
there's
a "grand total" value. This value is located in the same cell on every
worksheet.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Much appreciated Tom,
JMay

"Tom Ogilvy" wrote in message
...
There was a typo on my part

Sub PrintOnlyCertainSheets()
Dim Sh as Worksheet
For Each Sh In ActiveWorkbook.worksheets
If Not IsEmpty(Sh.Range("B5")) Then
Sh.PrintPreview
End If
Next
End Sub

there is no reason to group sheets unless you want to work with a subset
of
all worksheets

Dim sh as Object
for each sh in ActiveWindow.SelectedSheets


--
Regards,
Tom Ogilvy


JMay wrote in message
news:0WtAb.47637$yM6.32904@lakeread06...
From you suggestion I've create a WB with Sheet1-Sheet3
And in only Sheet1 Cell B5 I have entered "444".
Grouping All Sheets before running the below I Get an error:
"Circular Dependencies between Modules"
with For Each Sh In ActiveWorkbook highlighted

Sub PrintOnlyCertainSheets()
For Each Sh In ActiveWorkbook
If Not IsEmpty(Sh.Range("B5")) Then
Sh.PrintPreview
End If
Next
End Sub

What have I done wrong?
TIA,,
JMay

"Tom Ogilvy" wrote in message
...
for each sh in activeworkbook
if not isempty(sh.Range("B30")) then
sh.printout
end if
Next

--
regards,
Tom Ogilvy

hailnorm wrote in message
...

I need a print macro that only prints worksheets with data on it.
Basically, I need excel to print each and every worksheet where
there's
a "grand total" value. This value is located in the same cell on
every
worksheet.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

I tried to use the macro that you had suggested this week & I get a
error:

Quick recap: I created a file with 4 worksheets (Qtr1, Qtr2, Qtr3
Qtr4) totaling data. The totals vary from sheet to sheet- the cel
location of the 'total' cell). So I linked the 'total' from each shee
to cell A1 within each of the sheets. A1 is formatted at white font s
it's not visible.

So if any of the 4 sheets has a value in cell A1 greater than '$0.00
then that particular sheet should print otherwise nothing happens.

I'm a novice when it comes to this so its probably something silly tha
I didn't do. Here is the macro


Sub SelectPrint()
For Each Sh In ActiveWorkbook
If Not IsEmpty(Sh.Range("A1")) Then
Sh.PrintOut
End If
Next
End Sub


If I run the macro with or without any values, I get the followin
error:

Runtime error 438
Object doesn’t support this property or method

“For Each Sh In ActiveWorkbook” is highlighted in yellow.

HELP!!!

--
Message posted from http://www.ExcelForum.com

hailnorm:

Tom's code line included the ".worksheets" property; you haven't included:

For Each Sh In ActiveWorkbook.worksheets

In addition your current code is testing whether Cell A1 is Empty or not,
isn't it?;
"I linked the 'total' from each sheet" << Sounds like A1 contains
something all the time, which means it is not empty, EVER... I say this
because once you fix the .worksheets + the code still might not work.

Seems like you might need to change it to:

Sub PrintOnlyCertainSheets()
Dim Sh As Worksheet
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Range("A1").Value < 0 Then
Sh.PrintPreview
End If
Next
End Sub

I hope this helps!!
JMay

"hailnorm " wrote in message
...
I tried to use the macro that you had suggested this week & I get an
error:

Quick recap: I created a file with 4 worksheets (Qtr1, Qtr2, Qtr3,
Qtr4) totaling data. The totals vary from sheet to sheet- the cell
location of the 'total' cell). So I linked the 'total' from each sheet
to cell A1 within each of the sheets. A1 is formatted at white font so
it's not visible.

So if any of the 4 sheets has a value in cell A1 greater than '$0.00'
then that particular sheet should print otherwise nothing happens.

I'm a novice when it comes to this so its probably something silly that
I didn't do. Here is the macro


Sub SelectPrint()
For Each Sh In ActiveWorkbook
If Not IsEmpty(Sh.Range("A1")) Then
Sh.PrintOut
End If
Next
End Sub


If I run the macro with or without any values, I get the following
error:

Runtime error 438
Object doesn’t support this property or method

“For Each Sh In ActiveWorkbook” is highlighted in yellow.

HELP!!!!


---
Message posted from http://www.ExcelForum.com/