VBA Paste Formulas Across Worksheets to Last Row of Data

[LALD]

Hi,

I have a macro that splits my data by column x and pastes it into new worksheets for each unique item in column X. However, while the formatting is preserved I lose all the formulae. Is there a way of preserving the formulae while I do this? Or would I need to create a separate macro to copy the formulas from the original worksheet across all the new tabs created? The problem I have is that each worksheet has a different amount of rows so if it's a separate macro then it would need to use LastRow.

Here is the macro I have:
Sub LALD()
Set asheet = ActiveSheet
LastRow = asheet.Range("X" & Rows.Count).End(xlUp).Row
myarray = uniqueValues(asheet.Range("X20:X" & LastRow))
For i = LBound(myarray) To UBound(myarray)
Sheets.Add.Name = myarray(i)
asheet.Range("A19:CN" & LastRow).AutoFilter Field:=24, Criteria1:=myarray(i)
asheet.Range("A1:CN" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
Sheets(myarray(i)).Range("A1")
asheet.Range("A19:CN" & LastRow).AutoFilter
Next i
End Sub
Private Function uniqueValues(InputRange As Range)
Dim cell As Range
Dim tempList As Variant: tempList = ""
For Each cell In InputRange
If cell.Value <> "" Then
If InStr(1, tempList, cell.Value) = 0 Then
If tempList = "" Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
End If
End If
Next cell
uniqueValues = Split(tempList, "|")
End Function

I am using Excel 2010.
Thank you in advance for your help. Much appreciated.

L

 

[LALD]

Further to my previous post, I have written a macro to copy the first row of data on the original sheet to the first row of data on the other sheets. Please can you advise how I amend this so that it copies the formulas down to the last row?
Sub CopyFormula()
ActiveCell.Offset(-3, 0).Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Mr X").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("Mr X", "Mr Y")).Select Replace:=False
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
ActiveCell.Offset(2, -85).Range("A1").Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Thanks so much.

L