Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count the number of worksheets used in a file?
I have a number of workbooks with a large number of worsksheets filled out
(not by me) in each workbook. Does anyone know how to count the number of worksheets used other than actually manually counting by clicking each worksheet? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count the number of worksheets used in a file?
You would need to use some VBA code -- you can't do it with just formulas.
For example, Sub AAA() Dim WB As Workbook Dim NumSheets As Long For Each WB In Application.Workbooks NumSheets = WB.Worksheets.Count Debug.Print "Workbook: " & WB.Name & _ " has " & CStr(NumSheets) & " worksheets" Next WB End Sub This code requires that the workbooks be open. You might want to post more details about what you want to accompish. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Suresh" wrote in message ... I have a number of workbooks with a large number of worsksheets filled out (not by me) in each workbook. Does anyone know how to count the number of worksheets used other than actually manually counting by clicking each worksheet? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count the number of worksheets used in a file?
=COUNTA('*'!A1)+1
assuming A1 in each sheet is non-blank. Kudos: Lori |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
# of workshhets in a file
Hi
I am trying to use =COUNTA('*'!A1) to calculate the number of non blank sheets I have in a workbook. It works well, however I need this to calculate for many cells and everytime someone adds a worksheet I have to re-enter the calculation as the * is replaced with the actual values each time. Is there anyway to change this or is there another solution? Herbert Seidenberg wrote: How do I count the number of worksheets used in a file? 15-Feb-08 =COUNTA('*'!A1)+1 assuming A1 in each sheet is non-blank. Kudos: Lori Previous Posts In This Thread: On Thursday, February 14, 2008 5:56 AM Sures wrote: How do I count the number of worksheets used in a file? I have a number of workbooks with a large number of worsksheets filled out (not by me) in each workbook. Does anyone know how to count the number of worksheets used other than actually manually counting by clicking each worksheet? On Thursday, February 14, 2008 6:02 AM Chip Pearson wrote: You would need to use some VBA code -- you can't do it with just formulas. You would need to use some VBA code -- you can't do it with just formulas. For example, Sub AAA() Dim WB As Workbook Dim NumSheets As Long For Each WB In Application.Workbooks NumSheets = WB.Worksheets.Count Debug.Print "Workbook: " & WB.Name & _ " has " & CStr(NumSheets) & " worksheets" Next WB End Sub This code requires that the workbooks be open. You might want to post more details about what you want to accompish. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Suresh" wrote in message ... On Friday, February 15, 2008 3:03 AM Herbert Seidenberg wrote: How do I count the number of worksheets used in a file? =COUNTA('*'!A1)+1 assuming A1 in each sheet is non-blank. Kudos: Lori Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk: Writing and using a custom referenced functoid. http://www.eggheadcafe.com/tutorials...-and-usin.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command to count number of worksheets in a workbook? | Excel Worksheet Functions | |||
Max number of worksheets in a file | New Users to Excel | |||
Simple Way to Count the Number of Duplicate Dates on Multiple Worksheets | Excel Discussion (Misc queries) | |||
count number of items in a row? help *file included* | Excel Worksheet Functions | |||
Count the number of worksheets in a workbook | Excel Discussion (Misc queries) |