Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting row in sheet (2003)

    Hello Everyone,

    I need some help. I need to create a macro that will insert a row via a macro or if they put any information on the last row before the total PCA hours sum. I am attaching the spreadsheet for references. Basically I want the PCA hours sum to be moving down as activities are added. Thansk.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting row in sheet (2003)

    Try this version:

    Sub ADD_ROW()
    Dim lngRow As Long
    lngRow = Range("B65536").End(xlUp).Row
    Rows(lngRow - 4).Copy
    Rows(lngRow - 3).Insert
    Application.CutCopyMode = False
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)-updated

    Thanks Hans. I have one problem with the macro above. If the last line has any info on it before the macro is run, it copies it which not what I had intended to do and the sum formula does not carry inserted cells. I still want the user to have the options of selecting facility type and activities in the inserted row. Any ideas or help. Thanks.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)

    Is it possible to have this run with a Worksheet_Change rather than having to run the code after each entry? What I am attempting to do is to have the sum row move down one row each time there is a new entry in the column. The code you have shown requires that I have to run the macro after each enter to get the sum row to move down.

    Not sure of syntax for Worksheet_Change.

    Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting row in sheet (2003)

    I think it's not a bad idea to let the user indicate explicitly that a new record is needed. You could put a command button on the worksheet that runs the macro when clicked.

    To use Worksheet_Change for this would be complicated.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)

    I agree about the button. I just was experimenting with Worksheet_Changes and thought that it might be possible.

    On a lighter note, I notice your reply is 23:59. Do you not sleep????

    Alos noted that my time was close to the same, but is only 16:00 plus. Assume that this site is running from some place in Europe.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)

    Any recommendations anyone? I do have a macro code that did something similar, but it assumed that there was nothing below the inserted sheet. Any help would be great?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting row in sheet (2003)

    Does the code I posted not work for you?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting row in sheet (2003)

    Actually, it is past 1 AM here. I usually sleep only 5 hours per night or so.

    Our server is in the US, but unless you set your time offset in Edit display preferences, you'll see UTC aka Greenwich Mean Time under (Adjustable) Time.

  10. #10
    Lounger
    Join Date
    Aug 2005
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)

    It does somewhat, but the sum formulas don't work and it copies the row information above when I run the macro above..

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting row in sheet (2003)

    Instead of using a macro try the new List feature of Excel 2003:

    - Select the range from A5 to the last data cell in column AH. Do *not* include the rows with Total PCA hours and below.
    - Select Data | List | Create List.
    - Click OK.

    When you enter data, a new row will be inserted when necessary, and the formulas will adapt themselves automatically. See what happens when you select a car make in A9 in the attached version.

  12. #12
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)

    Thank you Hans. This is what I need.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)

    Consider exploiting the Data|List|Create List option that your Excel 2003 comes with.
    Microsoft MVP - Excel

  14. #14
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting row in sheet (2003)

    Hans,

    Is there any way to protect some cells and still get the list feature? I try using the protect option in excel, but everytime I activate it, the option of adding another row for the list is deleted. Any help would be great. Thanks as aways.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inserting row in sheet (2003)

    I don't think the Excel 2003 List feature works well in a protected worksheet, but I cannot check until tomorrow. Perhaps someone else will have a definitive answer in the meantime.

Page 1 of 2 12 LastLast

Posting Permissions

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