I have a spreadhseet with som VBA code to copy down formulas in a number of columns to every row with data in column A - I have run into a bit of trouble as the data in column A either comes from a userform or is copied and pasted from another seperate worksheet - I have tried my code with it set on Worksheet_Change(ByVal Target As Range) event and works when userform writes new entry , however when the information comes from the other worksheet , this doesnt work unless I manually click my mouse into the last entry then press enter

Anyone got any ideas how I can make my code work regardless of the method of entry of the data, i tried re-wrting the code to work on worksheet activate and activating it after my copy from another workbook code has run but again this only works if I manually click on my data sheet

I am only new to VBA so got a bit stuck on what is probably a simple issue , tried searching for the answer to this but to no avail 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lCellCount As Long
    Dim lFormulaCount As Long
    Dim oWorkSheet As Excel.Worksheet
    Dim oRangeSource As Excel.Range
   Dim oRangeDest As Excel.Range

  ' 'Define sheet
   Set oWorkSheet = Worksheets("Data")

   ''Count how many entries we have in our dataset now in A1
    
    lCellCount = oWorkSheet.Range("A1").End(xlDown).Row

    ''Count how many formulas we have to proper delete
    
    lFormulaCount = WorksheetFunction.CountA(oWorkSheet.Columns("BL"))

  If lCellCount <> lFormulaCount Then

     '  'Delete the formulas
        
   If lFormulaCount > 2 Then oWorkSheet.Range("BL3:CQ" & lFormulaCount).ClearContents
   
          
       ' 'Copy from Here

       Set oRangeSource = oWorkSheet.Range("BL2:CQ2")
        
       ' ' to here
        
      Set oRangeDest = oWorkSheet.Range("BL2:CQ" & lCellCount)
        
      '  'Copy formulas

     oRangeDest.Formula = oRangeSource.Formula

   End If

End Sub
thanks in advance

trubster