Selecting Multiple Sheets

Hi,

(I know I've sent a similar post b4. But i tried very 
hard to find dat post and don't know why I can't find it. 
Tried searching for all posts posted by me in all threads)

I need a macro such that it'll (1) print the whole 
workbook, after that (2) select the first sheet up till 
the sheet called "Acc". (I can't put the name of the 
sheets coz they change all the time). (3) After selecting 
those sheets, the macro will change the font of cells 
(a1:a2) to red and then print the whole workbook again 
(including those not selected in (2)). After that, (4) 
the macro will select those sheets and change those cells 
back to white font.

I tried to start with a simple macro as shown below. Not 
only does it not work properly (the font does not change 
back to white, there's an error 400).

Sub Print_Invs()
  
    ThisWorkbook.PrintOut
    Sheets().Select
    Range("a1:a2").Select
    Selection.Font.ColorIndex = 3
    ThisWorkbook.PrintOut
    Sheets().Select
    Range("a1:12").Select
    Selection.Font.ColorIndex = 2
          
    
End Sub


Can someone please help me out?

Thanks & sorry for posting another thread. 

Regards,
Val
0
anonymous (74722)
9/2/2004 2:57:40 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
343 Views

Similar Articles

[PageSpeed] 45

First, I use Google to find old posts:

If you're looking for old posts, you can use google (maybe a few hours behind)
to search for stuff you've posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

and one way that seemed to work for me:

Option Explicit
Sub testme01()

    Dim testWks As Worksheet
    Dim wks As Worksheet
    Dim myAddr As String
    Dim DoneWithGroup As Boolean
    
    myAddr = "A1:A2"
    
    With ActiveWorkbook
        Set testWks = Nothing
        On Error Resume Next
        Set testWks = .Worksheets("acc")
        On Error GoTo 0
        
        If testWks Is Nothing Then
            MsgBox "no ACC worksheet!"
            Exit Sub
        End If
        
        .PrintOut preview:=True
        
        DoneWithGroup = False
        For Each wks In .Worksheets
            If wks.Name = testWks.Name Then
                DoneWithGroup = True
            End If
            wks.Range(myAddr).Font.ColorIndex = 3
            If DoneWithGroup Then
                Exit For
            End If
        Next wks
        
        .PrintOut preview:=True
    
        DoneWithGroup = False
        For Each wks In .Worksheets
            If wks.Name = testWks.Name Then
                DoneWithGroup = True
            End If
            wks.Range(myAddr).Font.ColorIndex = 2
            If DoneWithGroup Then
                Exit For
            End If
        Next wks
    End With
    
End Sub

(I kept the preview:=true to save some trees.)



Dolphinv4 wrote:
> 
> Hi,
> 
> (I know I've sent a similar post b4. But i tried very
> hard to find dat post and don't know why I can't find it.
> Tried searching for all posts posted by me in all threads)
> 
> I need a macro such that it'll (1) print the whole
> workbook, after that (2) select the first sheet up till
> the sheet called "Acc". (I can't put the name of the
> sheets coz they change all the time). (3) After selecting
> those sheets, the macro will change the font of cells
> (a1:a2) to red and then print the whole workbook again
> (including those not selected in (2)). After that, (4)
> the macro will select those sheets and change those cells
> back to white font.
> 
> I tried to start with a simple macro as shown below. Not
> only does it not work properly (the font does not change
> back to white, there's an error 400).
> 
> Sub Print_Invs()
> 
>     ThisWorkbook.PrintOut
>     Sheets().Select
>     Range("a1:a2").Select
>     Selection.Font.ColorIndex = 3
>     ThisWorkbook.PrintOut
>     Sheets().Select
>     Range("a1:12").Select
>     Selection.Font.ColorIndex = 2
> 
> 
> End Sub
> 
> Can someone please help me out?
> 
> Thanks & sorry for posting another thread.
> 
> Regards,
> Val

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/2/2004 9:21:24 PM
Reply:

Similar Artilces:

Multiple Selection Picklists
FYI, copied from www.SalesMetrix.net Create Multiple Selection Picklists in Microsoft CRM By using the onchange command on a picklist field along with a text or memo field, you can easily create the ability to capture multiple picklist values for situations where one value is not sufficient. For example, you may have a list of Account types and want to identify some Accounts as both customers and partners. To do this, we create a picklist of the values and then write java script behind the picklist to concatenate selected values to the memo field. Our memo field will become the list...

Select all paragraphs of a style, but only within selection
I selected a swath of text in Outline View. Within that swath, I would like to select all paragraphs of a certain style. Is this possible in Word 2003, on Windows XP? What would you like to do with the paragraphs, after you have selected them? Note that if you want to apply formatting, or apply a different style, you can use Find and Replace. -- Stefan Blom Microsoft Word MVP "Paul" <paul.domaskis@gmail.com> wrote in message news:bedc0f3f-557c-47a3-92d1-0e540ba456e2@f20g2000vbl.googlegroups.com... >I selected a swath of text in Outline View. Within...

Need Design Direction for Charting Spread Sheet
Here's the big picture. Design an Excel spreadsheet that will import a csv data file that is 575 colums wide (fields) and 720 rows (records) that I can then graph. Now the details. The purpose is to be able to graph historical data that is collected every two minutes (720 rows a day) for 575 different pieces of equipment, and I will only need to graph a couple things at a time. I started doing this through Access and imported the file into a 2-D Array since there is the 256 column (field) limit, then just pulled the data I needed out of the array and put it into a table which I could u...

Multiple Categories
I am new to Money.......I need to set up expense categories for each property, I have 8 cost centers (personal and properties) and need to categorize expenses for each ie: utilities, taxes, repairs, auto, food etc In microsoft.public.money, Bob wrote: >I am new to Money.......I need to set up expense categories for each >property, I have 8 cost centers (personal and properties) and need to >categorize expenses for each ie: utilities, taxes, repairs, auto, food etc OK. You can do that. On the other hand, you might find Classifications of use. Read up on those too before decid...

Multiple lines in a simple CEdit control
I am trying to do something that seems like it should be simple but... I am trying to write a multi-line string into an edit control on a dialog box. I form the CString by putting a \n between substrings of text. If I use CWnd::DrawText, the result works as desired, i.e. string1 string 2 etc If I use SetWindowText, the \n is not interpreted as a newline but just another character and I get a single line of text. Is there another way to add newlines in SetWindowText strings? Thanks in advance. Bob Did you set the ES_MULTILINE flag of the edit control? You can do that from the resource...

adding comment to multiple cells
Is it possible to add a comment to multiple cells without using copy / paste special? I would like to select the cells and add a comment to be viewed on rollover of any one of the chosen cells. Thanks for the help. Hi AFAIK this is not possible -- Regards Frank Kabel Frankfurt, Germany andy wrote: > Is it possible to add a comment to multiple cells without > using copy / paste special? > I would like to select the cells and add a comment to be > viewed on rollover of any one of the chosen cells. > Thanks for the help. ...

Change radio button when sheet is protected
I have two radio (option) buttons (from the forms menu, not controls), one to protect the sheet and one to unprotect the sheet. These are linked to cell $D$1 I also have code that protects/unprotects the sheet in order to sort the data. How can I get the radio buttons to change to reflect the current status of the worksheet (protected/unprotected) when the code runs to sort the sheet. My code: Sub SortRoom() Call UnProtSheet Application.Goto Reference:="DataEntry" Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Key2:=Range("C4") _ , Order...

Print multiple pages with different name.
I have set up a timesheet in excel that I would like to be able to print with each employees name on it. Is there a way of doing this without creating a sheet for each employee. Say you put the names of the employees on Sheet2 (a1:Axxx). And you want to print Sheet1 (and the names go into C1). Then you could run a little macro to print: Option Explicit Sub testme01() Dim myCell As Range Dim myNamesRng As Range Dim myHeaderCell As Range With Worksheets("sheet2") Set myNamesRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) ...

Selecting data from different cells for a chart
This is a little difficult to word without showing a picture of the data, but I'm wondering if there is a way to select data from different cells for a chart. The difficulty is that I have multiple sets of data that are organized in a series of cells..I'll see if I can explain this. Relative to the top of each box of data (box contains between 2 and 4 rows, with 6 columns across), the first cell I need (a data label) would be at 0,0. The second cell I need to pull from would be at 6,3 (6 columns to the right and 3 rows down). I need to pull this data repeatedly into a chart...

How to select all hidden text boxes
Hi, I have just operational knowledge of Excel. I have copied few cells from one excel file to new excel file. Source file contains some macros. I don't know, duew to this or what, in the new file, many hidden text fields are there. For that reason file size is more than 5 MB which actully should have less than 1 MB. I'm unable to select all the hidden text boxes at a time to delete them. Please guide me in this regard. Hi Manish, Try: '=============>> Public Sub Tester2() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets With sh ...

Enable multiple users with Email addresses
I am about to setup a new exchange 2003 server on our Domain. At the moment, we have Exchange 5.5 only. I have pretty much all users in the AD already, and I'm looking for way to mail enable multiple users at a time. My 1st step will be to create mailboxes for users that get mailboxes, but then I still have hundreds of users that need to be Mail Enabled. Almost all of these users have email addresses assiciated with them, but are not 'mail enabled' I know that I can individually right click, exchange tasks, establish email address, but that option doesn't come up when I ch...

Select multiple recipients.
If I want to send a message to specific recipients that I want to select from my Contact List at the time of sending that particular message, how do I do this? TIA Brian Tozer "Contact List" is an unclear term. Normally one would simply select the Contacts to which you want to send from the Contacts Folder > Actions > New Message to contact. -- Russ Valentine [MVP-Outlook] "KiwiBrian" <briantoz@ihug.co.nz> wrote in message news:di4p3a$tlm$1@lust.ihug.co.nz... > If I want to send a message to specific recipients that I want to select > from my Con...

How do you center the pie chart on the chart sheet
my default pie chart is way off center on the chart sheet. Do you know how to center it? Click and drag the plot area (click outside the pie, but inside the square that circumscribes it). There's no button to center the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Martie" <Martie@discussions.microsoft.com> wrote in message news:946A878A-DF95-4A27-8EAF-5F92AA61150B@microsoft.com... > my default pie chart is way off center on the chart sheet. Do you know > how &...

Change client permission on multiple Public folders
We have an Exchange 2003 Server with many public folders with subfolders (migrated from Lotus Notes). These are MAPI folders an have to be given access rights by using Exchange System Manager and give client permission manually on every single folder. Is there no "apply to all subfolders" add-in that exist? Or maybe a script? It takes some time to change permission on some 1200+ folders. Right-click on the parent public folder and pick "All Tasks". There is a "Propagate Settings" option there. "Bjorn Standal" wrote: > We have an Exchange 200...

Multiple Schedule C businesses
I have 2 businesses, that go to seperate Schedule C's. How do I make Money keep the 2 straight? For tax reporting purposes. I have seperate cheking accounts for the business, just not sure how to make the companies appear seperately for the tax transactions and tax software download. "James" wrote: > I have 2 businesses, that go to seperate Schedule C's. How do I make Money > keep the 2 straight? Go to "Set up your categories" and add new categories for your second Schedule C business. When you assign the tax line, also assign the category to Copy 2...

Use Same Variable multiple times in a query with different criteri
ok i have this variable i am trying to use twice with different criteria and it doesnt work help after i do with i want to count them November: Date_mailed (Variable Name) tblRecruitment2 (Table name) Between #11/1/2009# And #11/30/2009# (Criteria) October: Date_mailed tblRecruitment2 Between #10/1/2009# And #10/31/2009# I think you want to count? If so, set the E looking button on your tool bar, which turns on the Group ON feature then change the Group On to count. -- Milton Purdy ACCESS State of Arkansas "BZboarding101" wrote: ...

userform commnad from a sheet command
Hello I have the following piece of code in Excel sheet 1 to trigger an event when a cell content changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then 'This Code Runs When Cell A4 Changes' End If End Sub What I want to do is insert here code to interrupt the running of a particular userform and goto a particular point and resume that same userform. So something like Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then userform1.stop goto userform 1line 220 userform1.resume End If End Sub ...

Multiple Calenders in Outlook Today #2
I am playing with having multiple calendar folders (e.g., personal and business) so that I can print the personal calender with family events and hang it on my icebox. Works fine except that Outlook Today only shows events from a single calendar. Is there any way that OL Today can merge the events from more than one calendar? ...

Multiple Time Zones and the effects on All Day events in Outlook
Hello, I am working in a job that requires frequent travel between EST and MST time zones on a weekly basis. I like to switch the timezone on my system clock to account for which part of the country I am in. When I set up a calendar appointment for 9am while on EST, outlook will automatically set it to 7am when the system clock is switched to MST. This is good. However, any "All Day" appointments that I have set up (and I use this functionality alot) will also be pushed back two hours. This is not good, because outlook considers an "All Day" event to run from 1...

Data labels for selected points
How do I display a data label (showing the y-value) for a specific x-value? If I change the x-value, I want the data label reflect the new corresponding y-value. Add the x,y pair as a separate data series "Jade" <anonymous@discussions.microsoft.com> wrote in message news:30ba01c4c010$5aab4500$a301280a@phx.gbl... > How do I display a data label (showing the y-value) for a > specific x-value? > > If I change the x-value, I want the data label reflect the > new corresponding y-value. Tried the extra data series - it seems like a good solution How do I sel...

How do I print only one item on a page instead of multiple.
I'm trying to print out a badge in publishing on XP pro. and I only want to print out 1 badge and not a repeat of the same badge on the page. This was easy to do on 98 pub., all I had to do was x out the ones I didn't want on page preview. Is there a way to do this or am I goint to have to waste a sheet? "dummy06" wrote: > I'm trying to print out a badge in publishing on XP pro. and I only want to > print out 1 badge and not a repeat of the same badge on the page. This was > easy to do on 98 pub., all I had to do was x out the ones I didn't want on ...

Auditing Select Queries , so that the full query is logged
Hi, I currently looking into auditing, we want to see who (and when) has viewed/updated patient information on our database. (I am using SQL Server 2008 and our application is developed in VS 2008). I have set up an audit , and a database audit to log any selects for our patient table. However instead of showing the full select statement i.e. select top 1 * from patient where patientnumber = 111111 it is showing select top 1 * from patient where patientnumber = @1 So i also enabled change tracking on the database, this was great if i entered the query in management s...

Multiple email address in Outlook 2000
I am running Outlook 2000, I get my mail from a MS Exchange server. I have 2 e-mail address's and I receive e-mail to both addresses. Is it possilbe for me to choose which address e-mails are sent from? Also, I would like to know how to set up Outlook so if I reply to a message, the reply will be sent from the address that the original e-mail was sent to. Take a look at ChooseFrom application: http://www.ivasoft.biz/choosefrom.shtml -- Regards, Victor Ivanidze, software developer "Todd" <anonymous@discussions.microsoft.com> wrote in message news:188a601c41b...

How do I resize an excel sheet to fit the window view for a MDI
Hello, I am trying to automate an excel workbook using MFC. How do I resize it programmatically to fit the window view for a MDI? If any one could help, I would appreciate it. Thanks, Markus. ...

formula to search and find item sheet1 copy to sheet 2
i need to find an item in a colunm on sheet1 and auto copy that items row of info to sheet2. sheet1 changes with each import of data as to where "wings" may be on line 27 one time and move to 29 on the next import. so i need to search colunm B for "wings", when "wings" is found copy that row of info to sheet2 to create a list on sheet2.the formula will need to be adjustable per line, where as i can just change the item "wings" to "burgers" then insert the formula into line 2 sheet2.this will allow sheet2 to locate "burgers" on sheet1 ...