"Joe_Germany" wrote...
....
normally the function will be .....
=counta('sheet1'!D:D)
here we have to maually change the name for each sheets.

This formula would be in a worksheet other than Sheet1? If not, i.e., it'd
be in Sheet1, just use

=COUNTA(D:D)

OTOH, if you were putting together a summary worksheet with these results
from all other worksheets, you could do this without VBA but with another
worksheet, some defined names and some formulas.

Insert a new worksheet and name it ' ' (a single space, without the single
quotes). Define the name ' '!_WSLST (that's a worksheet-level name, so
include the ' '! before _WSLST) referring to the formula

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.DOCUMENT(88)&"]","")

Then select ' '!A1:IV1 and enter the ARRAY formula

=_WSLST

Then name that range WSLST (that's a workbook-level name, so no worksheet
name preceding it). The first named range uses XLM functions to return an
array of worksheet names. The array formula puts them into a worksheet range
(this prevents problems using references to XLM functions in older Excel
versions). That range may be referenced using the second name.

If your summary worksheet came first and ' ' second, and your detail
worksheets started with the third and went through the 102nd worksheets, you
could enter the following formula in cell B2 of the summary worksheet.

B2:
=IF(ROWS(B$2:B2)+2<=COUNTIF(WSLST,"*"),
COUNTA(INDIRECT("'"&INDEX(WSLST,ROWS(B$2:B2)+2)&"' !D:D")),"")

This should return the number of entries in column D of the first detail
worksheet which is the third worksheet in the workbook/file. Fill this down
into B3, and it becomes

B3:
=IF(ROWS(B$2:B3)+2<=COUNTIF(WSLST,"*"),
COUNTA(INDIRECT("'"&INDEX(WSLST,ROWS(B$2:B3)+2)&"' !D:D")),"")

which should return the number of entries in column D of the second detail
worksheet which is the fourth worksheet in the workbook/file. Fill this down
into B4:B101 to get the numbers of entries in columns D of the other detail
worksheets.