Hi Andrew,

I am not sure that I fully understand. Firstly you said that you recorded
ActiveWorkbook.Names.Add Name:="Counting", RefersToR1C1:="='B ACC'!C13"
I think the recording would have shown the cell reference as R13C3.

Now I am assuming that strAsheet is the variable that you want to use in
place of the actual sheet name in defining a name for cell C13 so here is a
little sample code that might help.

Dim strAsheet As String

strAsheet = "MySheet" 'You can assign the string from your table.

ActiveWorkbook.Names.Add Name:="Counting", _
RefersToR1C1:=Sheets(strAsheet).Range("C13")

Note that the above does not use the same syntax as applied when recording
the code. Also note the space and underscore at the end of a line is a line
break in an otherwise single line of code.

Feel free to get back to me if you are still having problems

--
Regards,

OssieMac


"Andrew at Fleet" wrote:

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