Add numbers of rows according to cell value

[CutandPaste01]

I've got a spreadsheet where the user enters information about an organisation including one cell where they enter the total number of employees. This is on one worksheet "Organisation"
I then want them to enter information about each employee on another Worksheet "Employees". This information will be in rows and only numbers will be stored:
Employee1:Info1:Info2:Info3
Employee2:Ino1:Info2:Info3
and so on.

At the bottom of each column I want to add the totals. I don't need to pull in any info about the employee as the user can overtype Employee1 etc.

What I want to do is after entering the number of employees in the cell I want the same number of rows to be automatically generated so that there is a row for each employee. This could be done from a button.

I'm totally new to Excel VBA so any help would be appreciated!

 

[NateO]

No need for a button...Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRw As Long
myRw = 1 'change this row number where you prefer insert (repost if variable)
'Set range below for range where # employees will be entered
On Error GoTo 1 'error trapper, in case of text entry
If Not Intersect(Target, [a3:a65536]) Is Nothing Then _
    Sheets("Employees").Rows(myRw).Resize(Target.Value).Insert
1: End Sub

See the notes after the '. Right-click on your 'organisation' sheet and paste this code. If you enter a number in the cell range a3:a65536, you'll get new rows in your 'employees' sheet equal to the value entered. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-08 20:02

 

[CutandPaste01]

Thanks for the reply, I'm going to have a go at this. How would I modify your code if I wanted to ensure that:
A) Say I had a formula in one of the columns how can I get that to be copied as well (just row totals).
B)Ensure that a column total at the bottom of each column recalculated with the right cells (including all the new ones)?.

I can post a demo spreadsheet if required.

Many thanks,
CutandPaste01

 

[NateO]

On 2002-05-09 00:30, CutandPaste01 wrote:
Thanks for the reply, I'm going to have a go at this. How would I modify your code if I wanted to ensure that:
A) Say I had a formula in one of the columns how can I get that to be copied as well (just row totals).
B)Ensure that a column total at the bottom of each column recalculated with the right cells (including all the new ones)?.

I can post a demo spreadsheet if required.

Many thanks,
CutandPaste01

Let me see.

A) Where is the formula (sheet and range)? And where are you copying it to (sheet & range)?

B) Make sure the row to perform your insert is one row above your sum functions. You'll probably want a blank row (reduce height or hide) in between your data and the sum functions. The sum function will automatically update. I think they will recalc. automatically, if not, stick:

calculate

At the very end of this procedure, just above end sub.

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-09 14:21