+ Reply to Thread
Results 1 to 14 of 14

Insert range-named row at user-selected row

  1. #1
    Registered User
    Join Date
    05-03-2008
    Posts
    9

    Insert range-named row at user-selected row

    Hi folks. First post.

    On the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet.

    If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position).

    This is what I have tried so far:
    Please Login or Register  to view this content.
    Needless to say, it's not working as well as I would like. I could really use some help. Thanks.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Welcome to the Forum.

    Assuming your "template" row is in Row 3 then this will copy the contents of row 3 to the activecell's row

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    05-03-2008
    Posts
    9
    Thanks for the help, Roy. I've learned something new.

    The code that you've supplied destructively overwrites the entire row at the current user position. This is what I would do in Excel (not Visual Basic) to accomplish my goal. Assume that my cursor is at D24.
    • Highlight the entire first row by clicking the row index.
    • Copy the entire row: Ctrl+C
    • Hightlight the entire row below where I want the copied row inserted
    • Right-mouse-click, then select "Insert Copied Cells"
    • Click on the cell at D24
    How can this sequence be replicated using Visual Basic?

    By the way, when I use the word "template", it's because this file is in fact an XLT file.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Maybe like this?
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-03-2008
    Posts
    9
    Please Login or Register  to view this content.
    Returns the following message (referring to the first line of code):
    Run-time error '438':
    Object doesn't support this property or method

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    is the code name of the worksheet Production/ If not try

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-03-2008
    Posts
    9
    Excellent! Thanks for the collective input.

    Now that it works with my XLT template, I want to make sure that it will work with any XLS instance that's created when that template is opened. My guess is that I'll need to replace the Workbooks("SPO-master.xlt") with something more dynamic.
    When the user does a File | New, then selects SPO-master.xlt an instance of the template is created — temporarily called SPO-master1.xls. How might I adjust the macro code above so that this filename (or any new filename that user might want to assign) is properly applied (ensuring that the macro functions properly)?

    Thanks, folks.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I'm not sure exactly what you're tring to do, but maybe this:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-03-2008
    Posts
    9
    Please Login or Register  to view this content.
    How can I change this line so that it will copy the entire row one of whatever the filename might be? Maybe something like this:
    Please Login or Register  to view this content.
    mercí

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Did you try the code I posted?

    Please Login or Register  to view this content.
    Last edited by shg; 05-04-2008 at 02:18 PM.

  11. #11
    Registered User
    Join Date
    05-03-2008
    Posts
    9
    Yes, I did. Thanks. I got an error message that told me I already had an SPO-master.xlt file open. From what I can tell, your code does the following:
    • Creates a variable r to store reference to the ActiveCell's row index.
    • Creates a variable wkb to store reference to the ActiveWorkbook.
    • Save reference to the active cell's row index in the variable r.
    • Creates a new workbook, using the SPO-master.xlt template file (located at some storage location).
    • Assigns the name of the active workbook to the variable wkb.
    • Copy row 1 from the "Production" sheet of the current workbook to the clipboard.
    • Insert that copied row just below the active cell's row (r) of the active workbook (wkb).

    OK ... I must have had my eyes half closed when I went through the code the first time. You seem to have hit the nail on the head. Using the elements contributed above:
    Please Login or Register  to view this content.
    Because row 1 that I want to be copied/inserted is hidden, I've added a line.

    What minor change must I make to insert the copied row below the active cell (it now inserts above)?

    gracias

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Creates a variable r to store reference to the ActiveCell's row index.
    r is a range variable; it references the entire row.
    ... change must I make to insert the copied row below the active cell (it now inserts above)?
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    05-03-2008
    Posts
    9
    Fabulous! Thanks for all your help.

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You're welcome, glad it worked out for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1