Copy automatically formula and formatting to next row

joherve

New Member
Joined
Sep 22, 2015
Messages
3
Hello
Im new with VBA but can understans. I need help to copy the entire row (formula + formatting) on the empty row downward while hit enter or typing on it.
Thank you
 

LloydFinancials

Well-known Member
Joined
Apr 24, 2015
Messages
509
This code pastes formulas and formatting from column A into column B on doubleClick of cell in the respective range of column B.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Application.ScreenUpdating = False
   lastrow = Cells(Rows.Count, 1).End(xlUp).Row
   Set Target = Range("B1:B" & lastrow)
   
   'The following for loop updates formulas in column A to absolute references. _
   Delete if not necessary.
       For Each c In Range("A1:A" & lastrow)
           c.Value = Application.ConvertFormula(c.Formula, _
             xlA1, , xlAbsolute)
       Next c
   
   Range("A1:A" & lastrow).Copy
   Range("B1:B" & lastrow).PasteSpecial Paste:=xlPasteFormats
   Range("B1:B" & lastrow).PasteSpecial Paste:=xlPasteFormulas
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
End Sub
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,535
Welcome to the Board!

If you format your existing data range as a table, then any data input in the row immediately beneath it will automatically extend formulas/formatting. No need for VBA...

HTH,
 

joherve

New Member
Joined
Sep 22, 2015
Messages
3
Thank you for your answers.
I have try to use table but it doesn't work because the formula starts on the 2nd row.
my data is like this example:
No-----FROM------TO-------DIFF
1-------0------------4----------4
2-------4------------10.3-----5.7
3-------10.3--------13.3-----3
......and so on
and the end is variable according to each sheet. Maybe from 60 to 2000rows.
I have just to enter data on "TO" column .
The formula are:
[No]: always starts from 1 and I would like that it become 2 when I enter data on cell C3.
[FROM]: always starts from 0. Also I d like that cell B3 = C2 if I enter data on cell C3.
It is the same for the [DIFF]: Difference
So how to copy the formula and the formatting on the next rows.
Thank you.
 

Forum statistics

Threads
1,077,662
Messages
5,335,561
Members
399,024
Latest member
rokcel389

Some videos you may like

This Week's Hot Topics

Top