Or just use a formula in column J:

This would go in J2:
=counta(indirect("'"&d2&"'!m:m"))-1

Or to check for a missing worksheet:
=if(iserror(cell("address",indirect("'"&d2&" acc'!a1"))),"missing",
counta(indirect("'"&d2&" acc'!m:m"))-1)


Don Guillett wrote:

Have you tried this simple approach. Change variables to suit or use an
array
Sub placeformula()
strASheet.Range("b2").Formula = "=counta(sheet1!m:m)"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andrew at Fleet" wrote in message
...
To whom it may concern,

I'm trying to insert a COUNTA funtion in a cell, which refers to a range
set
in different worksheets, but I'm having problems, & was hoping to find
some
help.

I have a macro that I'm writing for Excell 2003, which creates multiple
reports, with 4 types of sheets each. I'm using cell values from a Table
sheet to create Strings, which will select different sheets, with the
following code;
Set TabACell = ActiveCell ' Company
Set TabDCell = TabACell.Offset(0, 3) ' Company Initial
Set TabJCell = TabACell.Offset(0, 9) ' Count Copied
strASheet = TabDCell.Value & " ACC"
strFSheet = TabDCell.Value & " FLEET"
, etc. Then, I want to count the non-empty cells in a column in each
sheet.
I recorded the following macro;
Sheets("B ACC").Select
Columns("M:M").Select
ActiveWorkbook.Names.Add Name:="Counting", RefersToR1C1:="='B ACC'!C13"
Sheets("Table").Select
ActiveCell.FormulaR1C1 = "=COUNTA(Counting)"

Then changed it to suit my table sheet;
Sheets(strASheet).Select
Columns("M:M").Select
ActiveWorkbook.Names.Add Name:="Counting",
RefersToR1C1:="='strASheet'!C13"
Sheets("Table").Select
TabJCell.FormulaR1C1 = "=COUNTA(Counting)-1"

This seemed to be a good solution to me, as the TabDCell values change for
each report. I would then delete the Name created, & loop the code for
the
next report. But, the macro is having trouble with the new reference that
I
have written. Considering that the string values will change. How can I
re-write RefersToR1C1:="='B ACC'!C13" so that it uses the string created
from
the table values?


TIA
--
Andrew
Telstra Fleet
Melbourne
Australia

--

Dave Peterson