copy formula from last populated cell in row

[redspanna]

Hi all

can somebody show me how I can search through row 8 of my 'sheet1' to find the last populated cell, then copy the formula of that cell into the next cell.

for example if E8 is last populated cell then copy the formula in E8 into E9

thanks

 

[jim may]

Paste this code into your Sheet Module then just Double-Clicj the Row you wish for this action to take place..


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
LC = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
Cells(Target.Row, LC).Copy Destination:=Cells(Target.Row + 1, LC)
End Sub

 

[redspanna]

Hi Jim

Can this be done without having to double click on the row?
reason I ask is that this procedure will form part of a bigger routine

thanks

 

[jim may]

In a standard Module enter - Before running click on the row of interest..

Sub Foo()
LC = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
Cells(ActiveCell.Row, LC).Copy Destination:=Cells(ActiveCell.Row + 1, LC)
End Sub

 

[redspanna]

sorry my mistake...

for example if E8 is last populated cell then copy the formula in E8 into E9

I meant copy formula from E8 into F8, ie next cell along

thanks

 

[jim may]

Destination:=Cells(ActiveCell.Row, LC + 1)

 

[redspanna]

Great, thank you!