You should try turning on the "LIST" feature for your dataset. Put your
cursor in the data and press CTRL-L to activate the "List" wizard.
Once it is on, you'll see an "expansion row" offered at the bottom of your
data. If you type ANYTHING in that row, Excel will expand the "LIST" to
encompass this row permanently and it will copy any formulas from the row
above into that row as well as formatting.
This will also work if you click on a row in the middle of the data and
select "insert row". The "List" will always make sure new rows have the
needed and formatting and formulas.
-----
"Actually, I *am* a rocket scientist." -- JB
(
www.MadRocketScientist.com)
Your feedback is appreciated, click YES if this post helped you.
"Gotroots" wrote:
Hi,
I am looking to have a universal solution to adding a row in multiple sheets
at the same time.
Here is a macro I recorded which will add a row at row20 and copy formulas
in certain columns
'select tabs
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("Usual V to Z", " Usual R to U", _
" Usual Pto", " Usual P", " Usual H to O", _
" Usual F to G", " Usual C to E", " Usual B", _
" Usual A")).Select
Sheets("Usual A").Activate
'select row
Rows("20:20").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'filldown formula cells
Range("A19").Select
Selection.AutoFill Destination:=Range("A19:A20"), Type:=xlFillDefault
Range("A19:A20").Select
Range("C19").Select
Selection.AutoFill Destination:=Range("C19:C20"), Type:=xlFillDefault
Range("C19:C20").Select
Range("E19").Select
Selection.AutoFill Destination:=Range("E19:E20"), Type:=xlFillDefault
Range("E19:E20").Select
' ungroup sheets
Sheets("Usual A").Select
Range("B9").Select
The only problem with this macro is that it can only be used in its current
state to add a row at row20.
I need to be able to run code that adds a row based on which cell is
selected in column B
So for example if B31 is selected then the code would determine this is the
row where a row is to be added.
All the actions of the macro would then be run.
Hope someone can grasp what I am hoping to achieve, thank you if you can help.