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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,536
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,089,320
Messages
5,407,563
Members
403,152
Latest member
Junaid Azhar

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top