Automatically insert rows with formula

G

Guest

Hi my name is susy,

I'm working on a finacial structure that should be used for entry of data by
many person. I thought about locking the worksheet, but some else shoul be
able to insert a row a keep working withou messing with my formulas.

What should I do to automatically insert a row with the same formula at
least with the same formats?

susy
 
P

Pete_UK

You would have to do it with a macro - record a macro (in relative
addressing mode) while you do it once, then you can apply a short-cut
key or button to run the macro whenever you like.

Hope this helps.

Pete
 
G

Guest

HI,.........went to hlp to find outwhat is macro and what can I do with it
but the help explanatin are vague. I'm not familair to all th epossibilities
in Excell.

For example help told me to record a code,.......what do they mean with
code, formulas? and if so what fomula should I apply in thiss case?
 
I

ilia

They mean Visual Basic code to program the spreadsheet to do a certain
behavior. In Excel 2003 and earlier, this functionality is found
under Tools->Macros. In Excel 2007, you will find it under the
Developer tab, which is hidden by default and must be enabled under
Excel Options.

When you use the "record macro" functionality, it will automatically
generate Visual Basic code, also providing you an option to assign a
shortcut key for this code. During recording, Excel will keep track
of everything you do, which will later enable you to repeat these
actions simply by running the macro or using the shortcut key
combination that you specified.

Many advanced users prefer to do this manually. For example, follow
this sequence of steps:

1. In your workbook (save a copy), press Alt+F11. You will see the
Visual Basic editor.
2. In VBE, under the Insert menu, choose Module.
3. Paste the following code:

Public Sub InsertRow()
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert Shift:=xlDown
End Sub

4. Close the VBE to return back to Excel.
5. In Excel, press Alt+F8 to show the Macros menu.
6. Select one called InsertRow, and press Run. A row will be
automatically inserted after your current cursor position.
7. Press Alt+F8 again, select InsertRow, and click on Options.
8. Go into the small shortcut key textbox next to Ctrl+ and press Shift
+R.
9. Press OK, then Cancel to close the Macros menu
10. In Excel, press Ctrl+Shift+R. A row will be automatically
inserted after your current cursor position.


Hope this helps.
 
J

JP

Hello,

You can protect the worksheet and still allow others to insert rows.
If you are in Outlook 2003:

1. Unlock and unprotect all cells (Ctrl-A, then Ctrl-1, Protection
tab, uncheck 'Locked' and 'Hidden')
2. Select the formula cells you want to protect, hit Ctrl-1,
Protection tab, check 'Locked' and 'Hidden')
3. When protecting your worksheet, under 'Allow all users of this
worksheet to", select 'Insert rows'

HTH,
JP
 
G

Guest

Hi thanx for replying to my post,............

I record a macro to insert a row it did function well and did record
everything I did. But the problem was that it did copy the exact formula
from Above. I did discover that without macro(i GUESS IT WAS WITHOUT MACRO)
when i insert a row manually it did copy the formulas from above ,....but
only when I insert som valeu inside of it .

Can I just keep it like that and Protect the cell?
 
G

Guest

Sorry for the confusuing but I'm wanted to ask

those Excell copy automatically the formulas from the row above, so I can
leave it with a macro?

or is this something I could have programmed with out knowing?

but I'll check you link thnx again 4 the help
susy
 
G

Guest

Hi Pete_UK,

just a questions if you compute a macro once and you delete it would you
have to "disactived" it some how to aviod it recoding later one another
action you take?

Because I try to recod a macro but after I delete it and reopened te
document excel ask me about recording a macro

susy
 
G

Guest

Hi it's me again,

This is exactly my problem. I some times excell does copy format and
formulas from the row above/below automatically. but other times it doesn't
and I need to just insert a row and when I insert my values my formulas will
automatically show it's outcome.

I did the following step but still it's like it's turn off.
please help me I Desperate :(

step explain by HELP:
Extend formats and formulas to additional rows
By default, Microsoft Excel automatically formats new data that you type at
the end of a range to match the preceding rows. Excel also automatically
copies formulas that have been repeated in the preceding rows and extends
them to additional rows.

Note In order to be extended to new rows in the range, the formats and
formulas must appear in at least three of the five preceding rows.

You can turn this option off (or back on again) at any time:

On the Tools menu, click Options.
On the Edit tab, clear the Extend data range formats and formulas check box
to turn automatic formatting off.
To turn automatic formatting back on again, select the Extend data range
formats and formulas check box.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top