Macro Error 1004 Application Defined or object defined error. PLEASE HELP

Hello I am trying to use a macro that links data from one sheet in a workbook to another sheet, and drags the formula across the rows.
My Macro works fine for one row at a time but I am trying to make the macro work down the entire column C.
Here is the code I am using:

Sub CopyData()
'
' CopyData Macro
' Use to copy and paste data from unix tab to FTE calculator tab
'
' Keyboard Shortcut: Ctrl+b
'
FinalRow = Cells(Rows.Count, 1).End(x1UP).Row
For i = 6 To FinalRow
Cells(i, 1).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[21]C[142]"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:AU1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:AU1").Select
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Next i

End Sub



I keep getting the error message:
Macro Visual Basic Runtime Error 1004 application defined or object defined error
I am using Excel 2010. Please help

Thank you so much! It worked! And thanks for you immediate responce!