Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Add numbers of rows according to cell value

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Code:
    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, NateO

    [ This Message was edited by: NateO on 2002-05-08 20:02 ]

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

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

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-09 14:21 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •