Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing multiple worksheets only if data exists
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing multiple worksheets only if data exists
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing multiple worksheets only if data exists
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing multiple worksheets only if data exists
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing multiple worksheets only if data exists
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing multiple worksheets only if data exists
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Multiple Worksheets | Excel Discussion (Misc queries) | |||
duplex printing multiple worksheets | Excel Discussion (Misc queries) | |||
Printing multiple worksheets. | Excel Discussion (Misc queries) | |||
Printing Multiple Worksheets | Excel Discussion (Misc queries) | |||
Printing Multiple Ranges from Multiple Worksheets | Excel Programming |