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,078,486
Messages
5,340,618
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top