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 |
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 |
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 |
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 |
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 |
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 |
Printing multiple worksheets only if data exists
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/ |
All times are GMT +1. The time now is 04:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com