ExcelBanter - Inserting rows

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Inserting rows (https://www.excelbanter.com/excel-discussion-misc-queries/217944-inserting-rows.html)

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--

Highlight the current set of data, all of it, and press CTRL-L to turn
the current dataset into an Excel "List". You'll see a box around the
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 down
to that row and copy all the formatting and formulas from the last row
into this new row. Very convenient.

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

Whats the criteria for adding extra rows?, what is the certain point?Vicki;199377 Wrote:
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--

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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.

"JBeaucaire" wrote:

Highlight the current set of data, all of it, and press CTRL-L to turn
the current dataset into an Excel "List". You'll see a box around the
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 down
to that row and copy all the formatting and formulas from the last row
into this new row. Very convenient.

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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" wrote:

Whats the criteria for adding extra rows?, what is the certain point?Vicki;199377 Wrote:
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--

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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

Vicki;199687 Wrote:
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" wrote:

Whats the criteria for adding extra rows?, what is the certain
point?Vicki;199377 Wrote:
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--

--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Inserting rows - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=54903)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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" wrote:

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

Vicki;199687 Wrote:
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" wrote:

Whats the criteria for adding extra rows?, what is the certain
point?Vicki;199377 Wrote:
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--

--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Inserting rows - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=54903)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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

Vicki;201774 Wrote:
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" wrote:

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

Vicki;199687 Wrote:
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" wrote:

Whats the criteria for adding extra rows?, what is the certain
point?Vicki;199377 Wrote:
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--

--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile:
Simon
Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd'
(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: 'Inserting rows - The Code Cage Forums'
('Inserting rows - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=54903))

--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Inserting rows - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=54903)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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

"JBeaucaire" wrote:

Highlight the current set of data, all of it, and press CTRL-L to turn
the current dataset into an Excel "List". You'll see a box around the
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 down
to that row and copy all the formatting and formulas from the last row
into this new row. Very convenient.

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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|
+-------------------------------------------------------------------+

--
noworries
------------------------------------------------------------------------
noworries's Profile: http://www.thecodecage.com/forumz/member.php?userid=109
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

Hi Vicki and thanks for joining, no problem helping you with that except
your worksheet is password protected, if its a password you use
regularly you can Private Message me with it, i will then be able to
proceed!noworries;203273 Wrote:
ok, here is the spreadsheet.

I really appreciate your help.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

password is payday

--
noworries
------------------------------------------------------------------------
noworries's Profile: http://www.thecodecage.com/forumz/member.php?userid=109
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903

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

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54903