Count Rows and Use to Populate Range

I want to count the number of rows in a worksheet, use the rowcount to define a global variable then use that row count to define a range to autofill the identified number of cells.

What I have that does not work is:
'This section declares variables for later use
Dim MillenniumLastCell
Dim MillenniumLastFormulaA
Dim MillenniumLastFormulaB
Dim MillenniumLastFormulaC
'
' This is the code for calculating the value of the variables
MillenniumLastCell = (Application.CountA(Range("A:A")) - 1)
MillenniumLastFormulaA = "A2:A" & MillenniumLastCell
MillenniumLastFormulaA = "B2:B" & MillenniumLastCell
MillenniumLastFormulaA = "C2:C" & MillenniumLastCell
'
'formulas get defined here and populate cells A2:C2
'
'This is the code to autofill the desired number of cells in colmun a with the formula in A2
Range("A2").Select
Selection.AutoFill Destination:=Range(MillenniumLastFormulaA)
Range(MillenniumLastFormulaA).Select

What the heck am I doing wrong?

I missed the typo on the variable definition and corrected that in my code but I still keep getting a 1004 error when trying to run it. The populated spreadsheet is too big to post. I get 2 extracts that I need to audit for discrepancies. The extracts can vary in length from a few hundred rows to 15K rows. For the audit I reformat the data and copy formulas into row 2 which I autofill from row 2 to the last identified row. What I do now is I have 3 different audit tools that are basically identical except for the number of rows they populate (5K, 10K, 15K). The more rows the slower the tool is and I'm also wasting time putting formulas in to evaluate blank rows. What I want to do is to count the number of rows on tab SourceDataM and use that count to define my autofill range for each column so it will only copy the formula the number of times required. I tried using an iterative loop, which worked but was painstakingly slow once it got over 5K rows.

So SourceDataM has 5123 rows. Currently I would have to use the 10K row sheet to evaluate it and I waste about 1/2 my time populating rows I don't need to audit. If I could have a way for the workbook to count the number of rows on the sheet and only copy the formulas the required number of times I think I could save a lot of time (and headaches from others who can't manage to grab the correct sheet and tell me that it's broken).

Count number of rows in worksheet SourceDataM
copy formula xxx to A2
copy formula xxx to B2
copy formula xxx to C2
recursively copy formula in A2 to range A2:A & SourceDataMRowCount
....B2....
....C2....
call macro2

sub() macro2
copy formula xxx to A2
copy formula xxx to B2
copy formula xxx to C2
recursively copy formula in A2 to range A2:A & SourceDataMRowCount
....B2....
....C2....

end sub
end sub
I also want to keep that count to use on other worksheets in the workbook for a similar operation.

BTW, this used to be a manual, row by row on printed paper process.

Here is the entire sub as it stands right now:

Sub Evaluate_Millennium_Extract()
'
' Evaluate_Millennium_Extract Macro
' Macro written 10/1/2009 by 20256
'
'This turns off screen updates to speed up the macro
Application.ScreenUpdating = False
'This copies the schedule name to the menu page
Sheets("Millennium Extract").Select
Range("A3").Select
Selection.Copy
Sheets("Menu").Select
Range("F8").Select
ActiveSheet.Paste
'This formats the schedule name to match the rest of the sheet
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 6
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 41
'
'
'This section declares variables for later use
Dim MillenniumLastCell As Integer
Dim MillenniumLastFormulaA As Integer
Dim MillenniumLastFormulaB As Integer
Dim MillenniumLastFormulaC As Integer
'
' This is the code for calculating the value of the variables
MillenniumLastCell = (Application.CountA(Range("A:A")) - 1)
MillenniumLastFormulaA = "A2:A" & MillenniumLastCell
MillenniumLastFormulaA = "B2:B" & MillenniumLastCell
MillenniumLastFormulaA = "C2:C" & MillenniumLastCell
'
'
'This deletes the schedule name from the Millenium extract
Sheets("Millennium Extract").Select
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
'This adds 3 columns necessary for comparisons
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
'Copies & pastes column headers from Headers sheet
Sheets("Headers").Select
Rows("19:19").Select
Selection.Copy
Sheets("Millennium Extract").Select
Rows("1:1").Select
ActiveSheet.Paste
Rows("1:1").Select
Selection.Font.Bold = True
'This section retrieves and pastes the formulas from the header's page
'***NOTE: the formulas are embedded in this macro instead of acutally copying them
' any changes to the Headers page is for reference use only
Sheets("Headers").Select
Range("A14:C14").Select
Selection.Copy
Sheets("Millennium Extract").Select
Range("A2:D2").Select
ActiveSheet.Paste
Range("A2").Select
'Formula is: =IF(ISBLANK(D2),"",IF((OR(G2=" ",G2="")),IF(J2=" NA","IGNORED",D2),G2))
'Formula Description:
' checks to see if parent test number is blank
' -if parent test number is blank it will return a blank
' - if parent test number is not blank it will look to see if the child test number is blank
' - if the child test number is blank or a space it will check to see if the price is "NA"
' - if the price is "NA" it will return "Ignored"
' - if the price is not "NA" it will return the parent test number
' - if the child test number is not blank it will return the child test number
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[3]),"""",IF((OR(RC[6]="" "",RC[6]="""")),IF(RC[9]=""NA"",""IGNORED"",RC[3]),RC[6]))"
Range("A2:D2").Select
Range("B2").Activate
'Formula is: =IF(A2="","",(IF(A2=D2,F2,H2)))
'Formula Description:
' checks to see if parent test number is blank
' -if parent test number is blank it will return a blank
' - if parent test number is not blank it will look to see if it is the same as the child test number
' - If the parent & child test numbers are the same it will return the parent test mneumonic
' - If the parent & child test numbers are NOT the same it will return the child test mneumonic
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",(IF(RC[-1]=RC[2],RC[4],RC[6])))"
Range("A2:D2").Select
Range("C2").Activate
'Formula is: =IF(A2="","",(IF(A2=D2,E2,I2)))
'Formula Description:
' checks to see if parent test number is blank
' -if parent test number is blank it will return a blank
' - if parent test number is not blank it will look to see if it is the same as the child test number
' - If the parent & child test numbers are the same it will return the parent test name
' - If the parent & child test numbers are NOT the same it will return the child test name
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",(IF(RC[-2]=RC[1],RC[2],RC[6])))"
'
Range("A2:D2").Select
Range("D2").Activate
'Formula is: =IF(A2="","",(IF(A2=D2,E2,I2)))
'Formula Description:
'
'---------------------------------------------
'This section removed for testing 1/22/10
'---------------------------------------------
'Copies formulas to first 7K rows
'Range("A2").Select
'Selection.AutoFill Destination:=Range("A2:A7000")
'Range("A2:A7000").Select
'Range("B2").Select
'Selection.AutoFill Destination:=Range("B2:B7000")
'Range("B2:B7000").Select
'Range("C2").Select
'Selection.AutoFill Destination:=Range("C2:C7000")
'Range("C2:C7000").Select
'
'---------------------------------------------
'End of section removed for testing 1/22/10
'---------------------------------------------
'
'---------------------------------------------
'This section added for testing 1/22/10
'---------------------------------------------
'
Range("A2").Select
Selection.AutoFill Destination:=Range(MillenniumLastFormulaA)
Range(MillenniumLastCell).Select
Range("B2").Select
Selection.AutoFill Destination:=Range(MillenniumLastFormulaB)
Range(MillenniumLastFormulaB).Select
Range("C2").Select
Selection.AutoFill Destination:=Range(MillenniumLastFormulaC)
Range(MillenniumLastFormulaC).Select
'
'---------------------------------------------
'End of section added for testing 1/22/10
'---------------------------------------------
'
'Replaces " --" in price column with "NA" to simplify cell evaluation
Range("J:J").Select
Selection.Replace What:=" --", Replacement:="NA", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:A").Select
'This formats the test number into ARUP standard 7 digits and pads length with leading zeros
Selection.NumberFormat = "0000000"
Columns("D:D").Select
'This formats the test number into ARUP standard 7 digits and pads length with leading zeros
Selection.NumberFormat = "0000000"
'Added 1/6/09
'Copy and paste formula values into cells instead of formulas themselves
Columns("A2:MillenniumLastFormulaA").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Sort data by evaluated test number field
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Rows("2:2").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
'This turns screen updates back on
Application.ScreenUpdating = True
End Sub