Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
Is it possible to automatically copy formulas from other rows to new
rows when adding a new line in a spreadsheet? I have cols A-AE of which cols A-N are inputted and the others are calcualated values from the data. How can I copy these formulas to the new cells when I add a new row of data? Currently I have several 'dummy' rows that contain the formulas that are used to input the data. Then I have to sort the worksheet to get the new rows inserted properly. I'd like to insert a new row at the proper location, enter the data and have the formulas copied to the new row. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
You may want to try this:
Tools|Options|Edit Tab|check "extend data range formats and formulas" Or you could use a macro... David McRitchie shares some code: http://www.mvps.org/dmcritchie/excel/insrtrow.htm look for: InsertRowsAndFillFormulas If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Steve M wrote: Is it possible to automatically copy formulas from other rows to new rows when adding a new line in a spreadsheet? I have cols A-AE of which cols A-N are inputted and the others are calcualated values from the data. How can I copy these formulas to the new cells when I add a new row of data? Currently I have several 'dummy' rows that contain the formulas that are used to input the data. Then I have to sort the worksheet to get the new rows inserted properly. I'd like to insert a new row at the proper location, enter the data and have the formulas copied to the new row. Any ideas? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
Dave mentioned the extend data feature and McRitchie site but since I find
entend data a little finicky and because I wrote a macro prior to Seeing Dave's reply, I'm posting it<g: Sub Macro1() With Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireRow) .Insert xlDown With .Offset(-1) .FillDown .SpecialCells(xlCellTypeConstants).ClearContents End With End With End Sub -- Jim "Steve M" wrote in message ... Is it possible to automatically copy formulas from other rows to new rows when adding a new line in a spreadsheet? I have cols A-AE of which cols A-N are inputted and the others are calcualated values from the data. How can I copy these formulas to the new cells when I add a new row of data? Currently I have several 'dummy' rows that contain the formulas that are used to input the data. Then I have to sort the worksheet to get the new rows inserted properly. I'd like to insert a new row at the proper location, enter the data and have the formulas copied to the new row. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
Dave,
I checked my options and the box you reference is checked and yet when I add a new row, the cells are not populated with any formulas. On Sun, 07 Sep 2008 11:24:12 -0500, Dave Peterson wrote: You may want to try this: Tools|Options|Edit Tab|check "extend data range formats and formulas" Or you could use a macro... David McRitchie shares some code: http://www.mvps.org/dmcritchie/excel/insrtrow.htm look for: InsertRowsAndFillFormulas If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Steve M wrote: Is it possible to automatically copy formulas from other rows to new rows when adding a new line in a spreadsheet? I have cols A-AE of which cols A-N are inputted and the others are calcualated values from the data. How can I copy these formulas to the new cells when I add a new row of data? Currently I have several 'dummy' rows that contain the formulas that are used to input the data. Then I have to sort the worksheet to get the new rows inserted properly. I'd like to insert a new row at the proper location, enter the data and have the formulas copied to the new row. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
the box you reference is checked
So now you know what I meant by finicky if per chance you saw my message. -- Jim "Steve M" wrote in message ... Dave, I checked my options and the box you reference is checked and yet when I add a new row, the cells are not populated with any formulas. On Sun, 07 Sep 2008 11:24:12 -0500, Dave Peterson wrote: You may want to try this: Tools|Options|Edit Tab|check "extend data range formats and formulas" Or you could use a macro... David McRitchie shares some code: http://www.mvps.org/dmcritchie/excel/insrtrow.htm look for: InsertRowsAndFillFormulas If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Steve M wrote: Is it possible to automatically copy formulas from other rows to new rows when adding a new line in a spreadsheet? I have cols A-AE of which cols A-N are inputted and the others are calcualated values from the data. How can I copy these formulas to the new cells when I add a new row of data? Currently I have several 'dummy' rows that contain the formulas that are used to input the data. Then I have to sort the worksheet to get the new rows inserted properly. I'd like to insert a new row at the proper location, enter the data and have the formulas copied to the new row. Any ideas? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
I agree with Jim. In fact, I've turned this off because I could never
understand how excel was going to "help" me. And instead of fighting it, I just figured it would be easier for me to do the work myself. Steve M wrote: Dave, I checked my options and the box you reference is checked and yet when I add a new row, the cells are not populated with any formulas. On Sun, 07 Sep 2008 11:24:12 -0500, Dave Peterson wrote: You may want to try this: Tools|Options|Edit Tab|check "extend data range formats and formulas" Or you could use a macro... David McRitchie shares some code: http://www.mvps.org/dmcritchie/excel/insrtrow.htm look for: InsertRowsAndFillFormulas If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Steve M wrote: Is it possible to automatically copy formulas from other rows to new rows when adding a new line in a spreadsheet? I have cols A-AE of which cols A-N are inputted and the others are calcualated values from the data. How can I copy these formulas to the new cells when I add a new row of data? Currently I have several 'dummy' rows that contain the formulas that are used to input the data. Then I have to sort the worksheet to get the new rows inserted properly. I'd like to insert a new row at the proper location, enter the data and have the formulas copied to the new row. Any ideas? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
Jim,
Thanks for the macro. I installed it and it works great. Just what I needed. On Sun, 7 Sep 2008 12:31:58 -0400, "Jim Rech" wrote: Dave mentioned the extend data feature and McRitchie site but since I find entend data a little finicky and because I wrote a macro prior to Seeing Dave's reply, I'm posting it<g: Sub Macro1() With Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireRow) .Insert xlDown With .Offset(-1) .FillDown .SpecialCells(xlCellTypeConstants).ClearContents End With End With End Sub |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying formulas when inserting new rows
Excellent!
-- Jim "Steve M" wrote in message ... | Jim, | | Thanks for the macro. I installed it and it works great. Just what I | needed. | | On Sun, 7 Sep 2008 12:31:58 -0400, "Jim Rech" wrote: | | Dave mentioned the extend data feature and McRitchie site but since I find | entend data a little finicky and because I wrote a macro prior to Seeing | Dave's reply, I'm posting it<g: | | Sub Macro1() | With Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireRow) | .Insert xlDown | With .Offset(-1) | .FillDown | .SpecialCells(xlCellTypeConstants).ClearContents | End With | End With | End Sub | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting multiple rows and copying formulas | Excel Discussion (Misc queries) | |||
need help please inserting multiple rows based on cell value then copying to data sheet | Excel Worksheet Functions | |||
Inserting formulas etc after n rows | Excel Discussion (Misc queries) | |||
Automatically copying fomulae when Inserting rows | Excel Discussion (Misc queries) | |||
Inserting Multiple Rows with Formulas | Excel Worksheet Functions |