Name and number of sheets

Could someone please post the code that will count the 
number of sheets in a workbook and get each sheet name. I 
thought I could use the for statement after I got the 
number of sheets, but can't seem to get that number.

Thank you
0
anonymous (74722)
3/18/2005 7:26:42 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
614 Views

Similar Articles

[PageSpeed] 17

Maybe something like:

Option Explicit
Sub testme()

    Dim wks As Worksheet
    
    MsgBox Sheets.Count
    MsgBox Worksheets.Count
    
    For Each wks In ActiveWorkbook.Worksheets
        MsgBox wks.Name
    Next wks
End Sub

Worksheets.count may not equal sheets.count (if you have chart sheets for
example).

MB wrote:
> 
> Could someone please post the code that will count the
> number of sheets in a workbook and get each sheet name. I
> thought I could use the for statement after I got the
> number of sheets, but can't seem to get that number.
> 
> Thank you

-- 

Dave Peterson
0
ec357201 (5290)
3/18/2005 9:53:45 PM
Reply:

Similar Artilces:

sequential number
I want to a sequential number to fill in automatically each time the form is filled out. Malissa, A simple way would be to use something like this, you could assign it to a button, an open or before print event. Sheets("Sheet1").Range("A1").Value = _ Sheets("Sheet1").Range("A1").Value + 1 For other ways to do this or if this is going to be used in a temple have a look here http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others...

Multiple name appearances
Have a list of 1000 names trying to sort by appearances >5. Not sure if Lookup or Hlookup? Pat318 wrote: > Have a list of 1000 names trying to sort by appearances >5. > Not sure if Lookup or Hlookup? If your list is in A1:A1000, put this in B1:B1000 and sort by column B: =COUNTIF($A$1:$A$1000,A1) ...

How to force special layout of File-as, Full Name and Company
When I enter a new business contact, "Johnny Rockets" for example, I want the Full Name, Company and File As to all show "Johnny Rockets" and not "Rockets, Johnny." Is there a way to cause that to happen without having to go back and adjust the items? Outlook 2007. vista. You can control it for new contacts - Tools, Options, Contact Options to set the file as format and the default name order. See http://www.slipstick.com/Tutorials/contacts/contacts.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook &...

Range Names
I need to figure out how to automatically assign dynamic range names to each "Saturday to Friday" in a list of monthly day numbers and associated day names for an entire year. A B 1 Sat 2 Sun 3 Mon 4 Tue 5........etc. I don't know where to start. Thanks, Wes Hi Wes, Depending on what you wish to do, XL's built in WeekNum function may assist. --- Regards, Norman "Wes" <additude@texstapes.com> wrote in message news:uV1CDQweFHA.3836@tk2msftngp13.phx.gbl... >I need to figure out how to automatically assign dynamic range name...

Question on Returns to Vendor without PO & Receipt number
Our company switched to Great Plains Dynamics from Simply Accounting 3 months ago. We currently need to return some of the inventory items to our vendors. However, when we try to enter a transaction entry in Purchasing/Returns Transaction Entry, in the detail lines we are asked for the PO number or the receipt number. We are not allowed to go further or save without the PO number or receipt number. When we converted from Simply Accounting, all we entered was the beginning balance of the vendors and only the PO's which we expect to receive. The older PO's were not carried over. W...

Emailing Tracking Numbers
I have integrated a website with RMS. When orders come in, I make them a workorder and send a PO to the distributor that will ship the product. At the end of the day, the distrbutor sends me the tracking number. When I bring up the customers order and enter the tracking number, I then complete the transcation, then press Shift/Control/F8 to mark the order processed. Does this send the tracking number to the customer? If not, how can I do that with out going to Outlook and physically doing so? Bill, RMS does not send one. Sounds like a good idea though. Maybe add it to the sugge...

Export to excel from ASP.Net
I have a page wherein the contents of a datagrid are exported to an excel file. All the columns are correctly displayed in excel except for the number columns. The formatting string for the number column is {0:#,##0.00; (#,##0.00);0.00}. The data is formatted for all numbers exceeding 1000 but for any number below 1000 the data is displayed as 1000 and not with 0's in the decimal places. (displayed as 999 instead of 999.00). ...

Name change due to marriage...
I have a user who recently got married and would like to have her new married name for her emails, but also wants external users to be able to send emails to her old address until she has adequate time to inform everyone. Her new email would be something like lastname-newname, firstname instead of lastname, firstname. Should I create a new account with her new name and forward all her emails from the old account to the new one and can I copy her current pst into the new one? THanks for everyone's help. No. Just highlight her account in ADUC, and choose re-name. Make the neede...

Comments on Manual Calculation on certain sheets.
I have a sheet in a workbook with array functions that slows down performance with calculation on automatic (which I need for the other sheets). Would it not have been useful to be able to set a single sheet to Manual and update when needed in the same way Pivot Tables work? One way to work around this is to save the Array Formula sheet in another workbook. I have browsed some of the posts on Excel's way of handling Automatic/Manual recalculation and it seems as if there is room for improvement or are there important reasons that I am not aware of? Any comments. Laurence Lombard Hi ...

Set up a script for a live performance with a cue call sheet
How do I set up a single one-sided sheet to follow a live performance script and be able to write stand-by notes and cues in a column beside the script with a timecode format ...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Window's verson numbers
I'm writing an app in C++ using VS.net and I need to do this based on the OS. I can get the OS version number using _osver global variable found in the include STDLIB.H. But I need a list of what version number correspond to what OS so I know what the value in _osver means. Does anyone know where I can find these definitions? Also the major and minor builds might be useful? If this is the wrong newsgroup could you direct me to the right one? Thank Lots, Neil Neil B wrote: >I'm writing an app in C++ using VS.net and I need to do this based on the OS. >I can get the OS ver...

copying text on sheet 1 to corresponding cells on sheets 2 to 5
I want the text entered in A8 on sheet 1(named Productivity) to automatically transfer to A8 on sheets 2 thru 5. I used the simple formula =Productivity!a8 in the corresponding cells but when there is no data in Productivity A8 I get a 0 in the corresponding cells. I would like the corresponding cells to remain blank if there is not text in Productivity A8. I am really new to excel, like only used it two weeks! Is there a simple fix to this? -- newbie at large! Hi A formula will always return 0 if it's refering to an empty cell. This formula will let the cell look empty if no...

2007 Slide Numbering doesn't work
I have a presentation in which the numbering doesn't work...I've gone step by step through everything I can find but I don't see it. Checked the master to ensure a placeholder was there, that the text is visible. I click on the slide thumbnail, click insert / slide number, check off slide numbering, click apply to all and okay. Nothing appears. When I go back to insert / slide number, nothing is checked. Any thoughts? Try ticking the option for slide number in Insert | Header and Footer instead. -- Echo [MS PPT MVP] http://www.echosvoice.com What's ne...

VBA Code to Open File1; Open File2 copy sheets to File1
Using XL 2003 & 97 Need help with the following VBA code: Sub Macro1() Dim myNewWorkbook As String myNewWorkbook = ActiveWorkbook.Name Workbooks.Open Filename:="OldWorkbookFile.xls" Sheets(Array("Formulas", "Department Lables")).Activate Sheets(Array("Formulas", "Department Lables")).Copy _ Before:=Workbooks(myNewWorkbook).Sheets(2) Windows("OldWorkbookFile.xls").Activate ActiveWorkbook.Close Windows(myNewWorkbook).Activate ' ........ End Sub My goal is to have myNewWorkbook active then:...

Faulting application name: iexplore.exe In Windows 2008 Server R2
I am not certain why I keep getting this exception message. In fact I get a pop up most each time I try to access or connect using IE. Did a bit of searching, and have not found a good fix for this yet. I am running a Windows Server 2008 64bit machine. I tried blaming it on IE add-ons, but now I am running in NO-ADDON mode with no add-ons but the problem persists.I would appreciate it if someone helped with with a fix for this on my server. ERRORS TYPES SO FAR ============================================= Faulting application name: iexplore.exe, version: 8.0.7600.16385, tim...

macro to generate next number
Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still suffici...

List number of permutations for text nums
I've got 4 digit text nums in A1 down In B1 down, I would like to list the corresponding number of permutations, eg: 7777 1 1777 4 0044 6 2477 12 1234 24 Game for any formula, udf or other vba solution Thanks for insights Sub permutations() Dim I As Integer, J As Integer, Rng As Integer Rng =3D Cells(Rows.Count, "B").End(xlUp).Row J =3D 1 Range("A1").Select Do Until ActiveCell.Value =3D "" For I =3D 1 To Rng Cells(J, "D").Value =3D "'" & ActiveCell & Cells(I, &qu...

List details in another sheet
Hi, I have a sheet "Data" with transactions by customer. Column A has the Customer ID. In another sheet called "By Customer", the user selects from a drop down a Customer ID, and clicks a button. What I want for this button is to run a macro to get in a 3rd sheet, "Transactions", columns B, C, D and E from the Data sheet, for the selected customer ID. Is that possible? Can somebody help? Thanks, Cecilia Use VLOOKUP http://www.ozgrid.com/Excel/excel-vlookup-formula.htm -- Regards Dave Hawley www.ozgrid.com "Chechu" <cecichi9@hotma...

Determine when Enter Key is pressed in a sheet??
I want to determine when Enter key is pressed in a sheet that is active?? I would like then to run a macro when the Enter key is pressed ?? The Enter Key is not a capturable event. Worksheet change is This added to the individual worksheet in VBA will trigger when ever the sheet is changed. Private Sub Worksheet_Change(ByVal Target as Range) 'Do Stuff End Sub If you are going to make changes to the sheet using this event is is usually a good idea to turn off events at the start and then turn them back on at the end. Otherwise the changes trigger this macro again....

Open excel sheet twice at same time?
Hi, Probably a question asked all the time, but I often have trouble opening a file after making a desktop shortcut to an excel file. When opening, it gives an error message about opening a file with the same name at the same time (even though I'm not). The error message continues by saying that it doesn't matter if the files are in different folders and suggests renaming one of the files. Any ideas? Thanks. Robert No reason I can think of that would cause Excel to load the file twice irrespective of launching it from a shortcut. Couple of things to check: - The file isn'...

page number
How do I remove page number from single pages, not only frontpage You can either insert a section break either side the un-numbered page and change the numbering in the new section or you can conditionally insert the page number in all the pages using fields e.g. {IF {Page} <> 4 "{Page}"} will insert the page number on all pages except page 4. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mv...

Convert Text to Number on 4,508 excel files.
Hi all. I'm faced with a rather unique problem.I have a large amount of improperly formatted excel files. They all have various cells formatted as text, yet they contain numbers and perform calculations on the cells. I now need to get these in a proper state. What I need is: Conversion of all text boxes containing numbers over to proper format of number. Do it automagically. I don't relish the thought of opening each one individually and fixing it. Any thoughts, tips or ideas on this? It could possible if you process that works by Scripting . In Scripting engine 5.6 Filesystem...

Adding User Name to footer
I have spreadsheets that go out to a number of users, and they make various changes and print out for themselves. The problems is that no-one knows which version to pick up from a central printer. Can I add the user name to the footer. I also have the path and file in the same area. I would like to end up with : User Name printed 'path/file' on 'date' ...

Excel 2003 - VBA
With code in Module 6, I created a button on a sheet as follows: ActiveSheet.Buttons.Add(1245, 16, 80, 16).Select Selection.Caption = "AutoFocus On" Selection.Name = "AutoFocusBut" Selection.OnAction = "FocusOn" Focus = True I placed the sub "FocusOn" also in Module 6. Public Sub FocusOn() If Focus = True Then .... .... When the button is depressed I get the message: "Ambigous name detected: FocusOn". What am I missing. Craig My bet is you have two 'things' named FocusOn. Two subs or a sub and a variable...