Inserting rows

[Vicki]

Hi,

I am working on a spreadsheet for our sales staff and I need the spreadsheet
to automatically create new rows once they get to a certain point so that
they don't run out of rows to enter their data for the month. How do I do
this?

Thanks for your help--

 

[JBeaucaire]

Highlight the current set of data, all of it, and press CTRL-L to tur
the current dataset into an Excel "List". You'll see a box around th
data and a star (*) in the blank row below the last set of data

If you start to type in the blank row, Excel will extend the list dow
to that row and copy all the formatting and formulas from the last ro
into this new row. Very convenient

 

[Simon Lloyd]

Hi

I am working on a spreadsheet for our sales staff and I need th
spreadshee
to automatically create new rows once they get to a certain point s
tha
they don't run out of rows to enter their data for the month. How do
d
this

Thanks for your help-

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Vicki]

The spreadsheet is currently blank. I am trying to get it ready to go for 5
sales people. Does there have to be info in the cells? When I highlight to
a certain point on the spreadsheet and then click Ctrl L it beeps at me. I
don't want all of the sheet to add more rows, I want the rows added above the
total line and formulas for the commissions.

 

[Vicki]

up to row 49 is where they enter their data and on row 50 the formulas start
for totaling up the columns. I have the rows 50+ locked so they cannot
tamper with my formulas.

 

[Simon Lloyd]

It depends what you want but this will add rows when each worksheet i
activated if there are more than 20 rows used with data in column
Code
-------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object
Dim i As Lon
If ActiveSheet.Range("A" & Rows.Count).End(xlUp) >= 20 The
For i = 1 To 10 'change to sui
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(-1, 0).EntireRow.Insert Shift:=xlDow
Next
End I
End Su
-------------------
to use it press Alt+F11 then double click the ThisWorkbook module an
paste it in

up to row 49 is where they enter their data and on row 50 the formula
star
for totaling up the columns. I have the rows 50+ locked so they canno
tamper with my formulas




Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
(http://www.thecodecage.com/forumz/showthread.php?t=54903

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Vicki]

I still can't get this to work. I have rows 50 + locked so that users cannot
change my formulas...could that be the issue?

 

[Simon Lloyd]

Vicki, if you wish you can join our forums (shown below) and attach
workbook to your post so we can help you directly with that, it'
completely free!, if you do join please post in this threa
http://tinyurl.com/c4roo9 so that people who have been helping o
following the thread may still do so

I still can't get this to work. I have rows 50 + locked so that user
canno
change my formulas...could that be the issue



0).EntireRow.Insert Shift:=xlDow


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
(http://www.thecodecage.com/forumz/showthread.php?t=54903

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Vicki]

sounds perfect. However, when I do that and press ctrl-l, it just beeps at
me. any idea why?

 

[noworries]

ok, here is the spreadsheet.

I really appreciate your help

+-------------------------------------------------------------------
|Filename: performance_pay(1).xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=69
+-------------------------------------------------------------------

 

[Simon Lloyd]

Hi Vicki and thanks for joining, no problem helping you with that excep
your worksheet is password protected, if its a password you us
regularly you can Private Message me with it, i will then be able t

ok, here is the spreadsheet

I really appreciate your help

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[noworries]

password is payda

 

[JBeaucaire]

Well, now I know why the LIST function didn't work, can't do that t
protected sheets. If you unprotect the sheet, you can activate the list
but once the sheet is protected I don't believe "automatically extend
itself one row as needed" feature works anymore