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