You have "DataColumnsMonth1" instead of "DataColumnsMonth" in both places
in your formula:

SUBSTITUTE(ADDRESS(1,DataColumnsMonth1,3,,)...

Is this what you want? Is there supposed to be a comma before the "1,2,,"
part?

Also, if you want to get the value of this named range, I would try:

Dim rngTotalNumberOfRowsData as Range
Dim TotalNumberOfRowsData as Long

Set rngTotalNumberOfRowsData =
Names("TotalNumberOfRowsData").RefersToRange

Application.Calculate
TotalNumberOfRowsData = rngTotalNumberOfRowsData.Value

Application.Calculate is used make sure the workbook has been calculated
(it may already be). The last statement should return the value of the
cell, which is what I think you want. I have never used the Evaluate
function when fetching pre-calculated values from worksheet cells. Try
stepping through this example and watch the Locals window.

I think you can also use a shorter version:

Dim TotalNumberOfRowsData as Long

TotalNumberOfRowsData = Range("TotalNumberOfRowsData").Value

....although I prefer the previous, slightly longer version, as it allows me
to see the reference to the cell (rngTotalNumberOfRowsData) in the Locals
window while stepping through the code (makes debugging easier).

HTH
--
Regards,
Bill Renaud