Incorporate a formula for the entire column using VBA

[Ramesha]

Hi Friends:

I, need help on copying the formula to entire column in the code belowI'm not able enclosed the excel file even though it is around 600kb!), it's in the end last few lines I'm struggling with!

Thanks in advance.

Sub data_transfer_3rd_sheet()

' Initial_set_up Macro
'

'
    'Prepare third worksheet to hold fresh data
    Worksheets(3).Activate
    ActiveWorkbook.RefreshAll
   
    Range("A1").Select
    'Copy source data from first worksheet
    Worksheets(1).Activate
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireColumn.Select
    Selection.Copy
    'Paste the copied data from first worksheet to third worksheet
    Worksheets(3).Activate
    ActiveSheet.Paste
    Columns("d:d").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("d:d").Select
    Selection.Insert: FormulaRC = "=LEFT(RC[-1],LEN(RC[-1])-10)"
       
End Sub

Hi Friends:

 

[Ramesha]

I'm working on inserting the formula to the entire column("d") in this case

 

[Ajesh]

Dim LstRow As Long '>> add this to declarations

LstRow = .Cells(.Rows.Count, "D").End(xlUp).Row
Range("D2").AutoFill Destination:=Range("D2:D" & LstRow)

Thanks/Ajesh

 

[Ramesha]

Thanks