Insert a macro that will insert a row and automatically copy formulas from the r
ID: 3560662 • Letter: I
Question
Insert a macro that will insert a row and automatically copy formulas from the row above
I have set up a spreadsheet which is split into ten sections relating to ten different types of activity. There are different formula in each of the ten sections, but the formulas are all in columns O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AN,AO,AQ,AR.
I want to set up a macro which means that if they click anywhere in any of the sections and click a button, a row will automatically be added, and the formulae copied down from the row above. It specifically has to be the row above as depending on which section of the spreadsheet they are adding lines to to formulae will be different.
Does anyone have any advice they can offer on this? I am not very experience in the use of macros so help would be much appreciated. The macro also needs to be able to work in Excel 2007 and 2003.
Explanation / Answer
*(*(*If we can get away with copying the entire row; and I suspect we can't because there may be data in 'other' cells you don't want to copy then we can get away with this which inserts a row below the active cell and copies the row above into it.@%
Sub InSert_Row()
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
End Sub
If we have to copy only those cells you mention then we can use this
Sub Insert_Row_2()
R = ActiveCell.Row
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
Range("O" & R + 1 & ":AA" & R + 1).Formula = Range("O" & R & ":AA" & R).Formula
Range("AN" & R + 1 & ":AO" & R + 1).Formula = Range("AN" & R & ":AO" & R).Formula
Range("AQ" & R + 1 & ":AR" & R + 1).Formula = Range("AQ" & R & ":AR" & R).Formula
End Sub
I wouldn't use a button to implement this because often the button wouldn't be visible. The way I'd implement this is. Alt+F11 to open vb editor. Right click 'ThisWorkbook' and insert module and paste in which ever code your going to use.
Back on the worksheet. Developer tab, macros, in the dropdown at the bottom select 'this workbook' and highlight the macro name. Options and assign a shortcut.