I am setting up a workbook to contain information on expenditure on events.
For each event there will be a worksheet detailing expenditure, which will
also act as a form for the expenditure to be authorised. The number of
worksheets will vary depending on the number of events held each month (i.e.
if there are 10 events in the month there will be 10 corresponding
worksheets). The data on these worksheets will be transferred onto a summary
sheet - one row per event. Rather than estimating the number of events per
month (and therefore the number of worksheets) and hardcoding the formulas
into the summary sheet, is there a command within excel that can count the
number of worksheets in the workbook so I can use the result to then populate
the summary page?

Try this tiny UDF:

Function worksheetcount()
worksheetcount = Worksheets.Count
End Function
--
Gary's Student


"Saul Fowler" wrote:

I am setting up a workbook to contain information on expenditure on events.
For each event there will be a worksheet detailing expenditure, which will
also act as a form for the expenditure to be authorised. The number of
worksheets will vary depending on the number of events held each month (i.e.
if there are 10 events in the month there will be 10 corresponding
worksheets). The data on these worksheets will be transferred onto a summary
sheet - one row per event. Rather than estimating the number of events per
month (and therefore the number of worksheets) and hardcoding the formulas
into the summary sheet, is there a command within excel that can count the
number of worksheets in the workbook so I can use the result to then populate
the summary page?

Hello

I'm not used to UDF, how does it work? I read that UDF should be created in
VBA module and that I have done, but how do I call it in workbook?

I have been fighting with this same worksheetcount problem for quite some
time...


"Gary''s Student" wrote:

Try this tiny UDF:

Function worksheetcount()
worksheetcount = Worksheets.Count
End Function
--
Gary's Student


"Saul Fowler" wrote:

I am setting up a workbook to contain information on expenditure on events.
For each event there will be a worksheet detailing expenditure, which will
also act as a form for the expenditure to be authorised. The number of
worksheets will vary depending on the number of events held each month (i.e.
if there are 10 events in the month there will be 10 corresponding
worksheets). The data on these worksheets will be transferred onto a summary
sheet - one row per event. Rather than estimating the number of events per
month (and therefore the number of worksheets) and hardcoding the formulas
into the summary sheet, is there a command within excel that can count the
number of worksheets in the workbook so I can use the result to then populate
the summary page?

Hi
I found the way to use UDFs just by luck, it was easier than I thought.
Now my workbook works like a dream :)
Thanks for this tiny UDF

"tuusis" wrote:

Hello

I'm not used to UDF, how does it work? I read that UDF should be created in
VBA module and that I have done, but how do I call it in workbook?

I have been fighting with this same worksheetcount problem for quite some
time...


"Gary''s Student" wrote:

Try this tiny UDF:

Function worksheetcount()
worksheetcount = Worksheets.Count
End Function
--
Gary's Student


"Saul Fowler" wrote:

I am setting up a workbook to contain information on expenditure on events.
For each event there will be a worksheet detailing expenditure, which will
also act as a form for the expenditure to be authorised. The number of
worksheets will vary depending on the number of events held each month (i.e.
if there are 10 events in the month there will be 10 corresponding
worksheets). The data on these worksheets will be transferred onto a summary
sheet - one row per event. Rather than estimating the number of events per
month (and therefore the number of worksheets) and hardcoding the formulas
into the summary sheet, is there a command within excel that can count the
number of worksheets in the workbook so I can use the result to then populate
the summary page?