Filldown Formula

[Carlie]

I have written this macro to copy and paste from several sheets and it works fine. However, I am having problems with the filldown formula in two seperate columns (Y5:Z5). Ideally, I would like the macro to filldown to the lastcell in the current range minus 1. The end result should resemble the table below.

columny columnz
Mth Stats Yr Stats Global% Global Agg%
Rate Rate
48.76 101.26
4.88 65.7 60.13% 9.28%
4.42 29.5 27.00% 8.24%
4.69 53.8 49.24% 8.74%
4.77 57.9 52.99% 7.66%
5.11 98


the macro runs up to the point highlighted in yellow

Can anyone help?

Thanks in advance


Sub CopyPaste()

Application.ScreenUpdating = False

Dim myfirstRow As Long, myLastRow As Long, myLastColumn As Long, n As Long, myformula As Range

Set myformula = Range("Y5:Z5")
n = Range("Y65536").End(x1up).Row

Range("A4").Select
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(myLastRow, myLastColumn).Address


myrange = "A4:" & mylastcell
'myformula = "Y5:Z5:" & mylastcell

Application.ScreenUpdating = True
Range(myrange).Select
Range(myrange).Clear

'Open Workbook
Workbooks.Open Filename:= _
"C:\Pathname"



Range("A10").Select

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(myLastRow - 1, myLastColumn).Address
myrange = "a10:" & mylastcell
Application.ScreenUpdating = True
Range(myrange).Select
Range(myrange).Copy

Windows("filename.xls").Activate

Range("A4").PasteSpecial

ApplicationAlert = False

Columns("Y:Z").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0.00%"
Range("Y5").Select
ActiveCell.FormulaR1C1 = "=RC[-23]/R4C[-23]"
Range("Z5").Select
ActiveCell.FormulaR1C1 = "=RC[-10]/R4C[-10]"
Range("Y6").Select
Range(Cell, Cells(n, cell.Column)).FillDown
Windows("filename").Close

[Carlie]

Please see atachment for example table of what macro should like when run.

Many thanks