Printing multiple worksheets only if data exists

  • Thread starter Thread starter hailnorm
  • Start date Start date
H

hailnorm

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
 
for each sh in activeworkbook
if not isempty(sh.Range("B30")) then
sh.printout
end if
Next

--
regards,
Tom Ogilvy

hailnorm said:
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.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
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
 
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
 
Much appreciated Tom,
JMay

Tom Ogilvy said:
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
Click to expand...
 
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!!!
 
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
 
Back
Top