Select sheets from an array for printing

I need Help!!
I have a workbook with hidden sheets, I want to search the visible sheets 
for those with value greater than 0 in C2 then print them.
Can someone show me where I am going wrong with this code?
Tahnks

Sub PrintSelectedSheets()
 'will print sheet with a value >0 in C2
  For Each Sheet In ThisArray.Sheets(Array("Sheet1", "Sheet3", "Sheet5", 
"Sheet7", "Sheet9"))
    Sheet.Activate
     If Range("C2").Value > 0 Then
     ActiveSheet.PrintPreview
     End If
   Next
 End Sub
0
bennyob (13)
11/24/2005 3:56:03 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
520 Views

Similar Articles

[PageSpeed] 40

Dim Sheet As Worksheet
Dim shStyle As Long
 'will print sheet with a value >0 in C2
  For Each Sheet In Sheets(Array("Sheet1", "Sheet3", "Sheet5", "Sheet7",
"Sheet9"))
     If Sheet.Range("C2").Value > 0 Then
        shStyle = Sheet.Visible
        Sheet.Visible = xlSheetVisible
        Sheet.PrintPreview
        Sheet.Visible = shStyle
     End If
   Next


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"bennyob" <bennyob@discussions.microsoft.com> wrote in message
news:163DB5EB-6E0D-4233-A7B7-EA01D23979BA@microsoft.com...
> I need Help!!
> I have a workbook with hidden sheets, I want to search the visible sheets
> for those with value greater than 0 in C2 then print them.
> Can someone show me where I am going wrong with this code?
> Tahnks
>
> Sub PrintSelectedSheets()
>  'will print sheet with a value >0 in C2
>   For Each Sheet In ThisArray.Sheets(Array("Sheet1", "Sheet3", "Sheet5",
> "Sheet7", "Sheet9"))
>     Sheet.Activate
>      If Range("C2").Value > 0 Then
>      ActiveSheet.PrintPreview
>      End If
>    Next
>  End Sub


0
bob.phillips1 (6510)
11/24/2005 9:00:30 AM
Thanks Bob!
Works a treat 

"Bob Phillips" wrote:

> Dim Sheet As Worksheet
> Dim shStyle As Long
>  'will print sheet with a value >0 in C2
>   For Each Sheet In Sheets(Array("Sheet1", "Sheet3", "Sheet5", "Sheet7",
> "Sheet9"))
>      If Sheet.Range("C2").Value > 0 Then
>         shStyle = Sheet.Visible
>         Sheet.Visible = xlSheetVisible
>         Sheet.PrintPreview
>         Sheet.Visible = shStyle
>      End If
>    Next
> 
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "bennyob" <bennyob@discussions.microsoft.com> wrote in message
> news:163DB5EB-6E0D-4233-A7B7-EA01D23979BA@microsoft.com...
> > I need Help!!
> > I have a workbook with hidden sheets, I want to search the visible sheets
> > for those with value greater than 0 in C2 then print them.
> > Can someone show me where I am going wrong with this code?
> > Tahnks
> >
> > Sub PrintSelectedSheets()
> >  'will print sheet with a value >0 in C2
> >   For Each Sheet In ThisArray.Sheets(Array("Sheet1", "Sheet3", "Sheet5",
> > "Sheet7", "Sheet9"))
> >     Sheet.Activate
> >      If Range("C2").Value > 0 Then
> >      ActiveSheet.PrintPreview
> >      End If
> >    Next
> >  End Sub
> 
> 
> 
0
bennyob (13)
11/24/2005 1:03:20 PM
Reply:

Similar Artilces:

Publisher won't print last page of my document
I publish a newsletter for my workplace. While printing the last 4 or 5 newsletters I find that I cannot print page 4 of my document (the last page.) I can print single pages, for example page 1 of 1, or page 2 of 2, but not page 4. In fact, if I include page 4 in the print (1 of 4), it won't print either. I have been emailing page 4 to another coworker who can print it just fine. On the same exact printer, mind you! Please help, very annoying. Thanks. What version Publisher are you using? What size is the stock? -- Mary Sauer http://msauer.mvps.org/ "jane...

Error message:Unable to display the selected folder or item. Could not open the item
I recently uninstalled Outlook 2003 in favor of using Outlook in WinXP. No when I try to start Outlook i ge the error message: "Cannot start Outlook. Cannot disply the selected folder ot item. Could not open the item. Try again." In another news group it was recommended to check the "pst" file. I have done a search and find not pst files. Help would be gratefully accepted!! Don Don <donsylvester@comcast.net> wrote: > I recently uninstalled Outlook 2003 in favor of using Outlook in > WinXP. No when I try to start Outlook i ge the error message: > ...

Sheet Protection #6
is it possible to protect and unprotect multiple sheets? Regards, Corey Corey, only with VBA, like this Sub protect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect password:="123" Next ws End Sub Sub unprotect_sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect password:="123" Next ws End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 20...

Auto selecting print area problem
I have a spreadsheet that is used by many people. For the majority, the setting I have for scale to fit to page works fine. However, there are several users of this spreadsheet that the area selected for printing is different. For the few people that have a problem, the print out looks very small where it looks like a postage stamp and font too small to read. This problem is consistent with the people that have this issue. The spreadsheet does not have "Print Area" defined in the page set-up intentially so it should automatically select the area for printing since sheets ca...

select by color
How can I select specific cells that have been colorized. Once selected I want to move these to a different column. Kristin N wrote: > How can I select specific cells that have been colorized. Once selected I > want to move these to a different column. Chip Pearson has a bunch of functions to handle colored cells: http://www.cpearson.com/excel/colors.htm Bill ...

Join / Merge sheet data to a consolidated list
Hello All Is it possible to join or merge data in multiple sheets to form a results sheet. eg. Sheet1 contains indeterminate number of records (variable unknown number of records) in column A, and an indeterminate number of similar records on Sheet2, Sheet3 etc. I want to read all entries in column A on all sheets and consolidate them all to one sheet for sorting purposes. Is this possible using a function in excel??? Thanks Jake Hi, Jake. This is something I do at work and is very easy. In fact, it is essential in a multi-person department setting to have a separate workbook for...

Selecting the last 10 records from a specific date
I have a date field and name field, I want to return the last 10 Name records entered from a specific date..... ie: Count back 10 records in the date field from Todays date and display the names from that date, to today.... seems really simple but I can't get it right... =?Utf-8?B?c2VtbWlzb24=?= wrote: > > I have a date field and name field, I want to return the last 10 Name records > entered from a specific date..... ie: Count back 10 records in the date field > from Todays date and display the names from that date, to today.... seems > really simple but I c...

Can't select text with mouse Outlook 2007 Windows 7
Can't use the mouse to select text in Outlook 2007 messages. (Works fine in Word 2007, Internet Explorer 8.) Using Windows 7. Thanks. Have the same problem. Allso in Word. If I restart the application (both Word and Outlook) it works for a few seconds and then the mouse stops working. Windows 7 64-bit and Office 2007 ...

Printing Multiple Sale Price Labels
We currently put 50 or so different items on sale each week. Normally we print out special price tage for these items to reflect the sale price. This involves going into each item, changing the price to the sale price, printing the label, and then changing the price back. Is there an easier way? Lance, We offer a RMS Add-in called RITE Label that knows when you put an item on sale, and from it's list you can see all items you put on sale on a given date. You can then choose which items to print labels for and how many copies to print. Very convenient, and this is only a drop in...

Is there a quick way to page breaks through out 25,000 line sheet
I need to place bage breaks in specific places on a sheet that has 25,000 lines. Is there an easy way to do this? 1) Type all row numbers where you want to declare Page-Break. 2) Copy/Paste the following macro to the VB Editor. 3) Select the range of cells and run that Macro. --------------------------------------- Sub AddPageBreaks() For Each CL In Selection Cells(CL + 1, 1).Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell Next [A1].select End Sub ------------------- Micky "Stacy" wrote: > I need to place b...

Unable To Read Certain fields in Linked Excel Sheet to Access
I am using Acc 2003, but Acc 2007 should work the same (I think). I have linked an excel spreadsheet into my Access database. Almost all of the fields are available and can be read. There are a few fields that I can see in Access, but its in Excel. These fields were using a VLookUp function in excel to get the value from another excel worksheet. Also the linked excel sheet has fields that I believe should be text (in Access), but its was set to be a number. I am unable to change this. Is there a way to resolve this issue for me ??? I also assume that if I was to rea...

Odd page printing
Is there anyway to print only the odd pages in Excel? Thank you. Hi Adam See http://www.rondebruin.nl/print.htm#odd -- Regards Ron de Bruin http://www.rondebruin.nl "Adam" <Adam@discussions.microsoft.com> wrote in message news:094EB827-D442-45AD-93E1-6618E43E5092@microsoft.com... > Is there anyway to print only the odd pages in Excel? Thank you. ...

Print Salesperson ID(s) on SOP Short Invoice
Salesperson is not assigned in Customer Master. During SOP Transaction Entry, one or more Salesperson(s) are assigned in SOP Commission Entry window. Salesperson ID's are not assigned per Line Item. Is it possible to print the Salesperson ID(s) on the invoice header. Thanks, Toni Are you wanting to print the salespeople from the SOP10101 table (Sales Commission Work and History) to the Invoice? I see a problem being able to get that to print on the invoice using Report Writer because of the table relationships. Report Writer does not to my knowledge allow more than one branch of...

How to save contents of cell range in an array?
In a VBA subroutine, how can one, for example, save the contents (numbers) of the range A1:L12 in the array x(12,12)? And how does one declare x()? Thanks in advance. John Uebersax dim x as variant x = Activesheet.Range("A1:L12").value Tim "John Uebersax" <jsuebersax@gmail.com> wrote in message news:8ef21027-a145-4022-8940-843b4cf2c943@j9g2000prh.googlegroups.com... > In a VBA subroutine, how can one, for example, save the contents > (numbers) of the range A1:L12 in the array x(12,12)? > > And how does one declare x()? > > ...

Trying to put a pivot table report on an existing sheet causes err
Thanks to Frank Kabel, I can now make great pivot tables. The problem I am having is that I want to write a macro to generate pivot tables, and I want the pivot table report to be saved on a worksheet called "Sum of Open", which is overwritten each time the macro runs. I have created the sheet named "Sum of Open" which is in the same workbook, but when i try to save it to there ('Sum of Open!'), I get an error: "Your formula contains and invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, ...

Excel Sheet generator
Hi I need a tool, that can analyse existing Excel Sheet, and generate Visual Basic 6.0 / VBA code, able to generate this sheet from scratch (add new blank sheet, apply all changes). I'm interested in column/row sizes, cell colors, borders, cell values, and formulas. I don't need support for VBA. Does anybody known such tool? Hi Tomasz: First create a template worksheet with all you formatting in place. Then its just a matter of pulling-down: Edit > Move or Copy Sheet... > and select template and click make a copy. If you are comfortable with VBA: Sub Macro1() Shee...

How can I set different print areas in the same Excel sheet?
I know very little about Excel since until recently all of my work was in Microsoft Word. However, people now email Excel documents to me to print and I get more confused with each one. Is it possible to set three different-sized print areas in one sheet, each with a different percentage of size adjustment? There are times when many pages of a sheet will go from A-H and the last one or two will go from A-AS. When each sheet is set to fit on a legal size page, the A-H pages are as microscopic as the last ones. What, if anything, could I do to even out the appearance and readabilit...

The SELECT permission was denied on the object
Hi I am trying to run the reports from crm 4.0 as a user and I get the error"The SELECT permission was denied on the object " However if I run the reports as admin they work fine. I have checked that the privreportinggroup,user group, sqlaccess group has select, connect and execute access to the MSCRM and the config database. I would really appreciate some suggestions here please. Regards CRMUser2008 Just to add on to the previous post, the reports are against filtered views not tables. I get the same error running any and every report under a user - even the stock standard ...

Event ID 61 when printing from Excel
I have a user on Excel 2000 who is trying to print multiple copies of a spreadsheet on a networked printer and gets frequent occurances of Event ID 61 with varying entries in the detail section. The printer queue looks normal, as if it is printing, at the PC level but nothing comes off the printer. Does anyone have any recommendations as to why this is occuring or what to do to fix it? Any information is appreciated! ...

Print Credit Card Receipts
I use PC Charge credit card processing. I can not get a merchant copy with a place for the customer signature to print. PC Charge support said it has to be programmed through RMS. When I open the receipt templates I don't have a seperate option for the credit card receipts. What can I do? Thanks, Dianne In your Tender Types (SO Manager, Database, Tender Types), you need to select "Require Signature" for the tenders that should print the Sig Line. In Receipt Formats (SO Manager, Database, Registers, Receipt Formats), you need to set the "Show CC Slip" (That may n...

Select Individual Records
I am a novice user, and know how to do simple queries. I have a table of 1,500 records, and would like the ability to select individual records for a report. Say, for example, I want to print labels for records 1, 432, 765, 134, and 1487. Those records may have nothing in common to do a query. Is there a way to do this? In my mind, their should be a way to create a query (or table or form or report) that shows all the records, allows you to highlight the records you want, then saves the results. Thanks -- Joe Hi Joe: This might sound a little on the clunky side, but in the ...

Printing mailing labels with multiple recipient name records associated with a single address
I have been browsing this, and other Access groups, as well as some of the Microsoft KB articles, and while I find posts decribing similar objectives to mine, can't seem to find anything that specifically shows me how to solve my problem. I have a property management database which contains many related tables, including a table "Units" and a table "Tenants". Tenant records have a corresponding foreign key for the UnitID relating them to the Units table. A tenant can only be associated with one Unit record, but a Unit may be associated with many Tenant records. I need ...

returning only a selected number of records
I have a tabel of questions and answers with autonumber. I have created a query that randomizes the autonumber so that the questions always appear in a different order when I run the query. The only problem is that I have about 1800 questions and I don't need all of them on a quiz. I know you can manually determine how many record are returned in a query by going to design mode and changing it. However I want to use a form that I always run before the query to determine the number of record returned. Like I have a textbox that I put X into and then run the query and it returns only X numbe...

Gantt ownership change fails when selecting user with deny New Pro
Our system is Project Server 2007 with SP2. For a division with a PMO department – all project managers can edit Gantts and create new Gantts. We want to change it so that only project managers which are PMO members can create Gantts. The PMO members will create the Gantts, change ownership to the rest of the project managers, and the project managers will continue from there. The change is to deny the single permission of creating a new Gantt from the project managers, which are not PMO members. I don’t want to create two separate and almost identical project mangers group...

my Excel chart doesn't print as it appears on print preview
my Excel chart doesn't print out to fit the page as it apppears to do on print preview. It always comes out shrunk in size. Take a look at Page Setup and adjust it to get what you want. "Doug 1014" <Doug 1014@discussions.microsoft.com> wrote in message news:FCE3AC95-13EA-4418-891C-0D79A84EECCE@microsoft.com... > my Excel chart doesn't print out to fit the page as it apppears to do on > print preview. It always comes out shrunk in size. I have done this several times both by using % reduction to make it fit and also using "fit to page", still com...