+ Reply to Thread
Results 1 to 3 of 3

Adding new row to the top of a table using macro button?

  1. #1
    Registered User
    Join Date
    11-10-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    2010
    Posts
    4

    Question Adding new row to the top of a table using macro button?

    EDIT: My client decided they want the data displayed differently, so I no longer need the answer. However, I'm curious how you'd go about this if anyone still wants to figure it out!


    I'm building a spreadsheet for my work to help organize maintenance data for all of our company's trucks. I'm not going to be the person entering data past creation of the project, so I want to make sure it's as simple as possible for anyone to add data to it.

    Now, by default when you add a new row to a table, you click the bottom right corner of the last row and hit TAB. I'd like to add a macro button that adds a new row to the TOP of the worksheet instead of the bottom. I'd like it to copy and past info from a (mostly) blank row that has some hidden data in a few columns.

    Here's a quick little simple mock-up of what I'm doing so I can explain why it needs to work this way.

    Capture.PNG

    Most of it is pretty self explanatory, the "tag data" columns are going to be hidden and only matter for pivot tables that will use this data (it's stuff like the vehicle number, make/model, etc for internal comparisons), and there's a total KMs, total service cost, and the "Next Service", which is just the current KM + 6,000 so we can project when a vehicle needs to be scheduled for service. The most recent service is at the top of the table, the oldest service is at the bottom. Believe me, I asked why we can't just have them swapped but this is how they want the data displayed so this is how it's gotta be.

    So I'd have my Add Row button, the macro would copy the data from the top row in the table under the header (Row 6 in my example), add a new row, shift the existing data down 1 row, paste the data into the new top row.

    Capture2.PNG

    There's going to be multiple tables in this sheet, one for each vehicle, since we're condensing down roughly 100 separate spreadsheets into one big one for ease of use and data analysis purposes.

    Seems straightforward enough but I can't quite seem to get it working!

    Edit: Unfortunately I can't post any links yet because I'm a new user.

    I've attached my example sheet though!
    Attached Files Attached Files
    Last edited by Ren_S; 11-16-2020 at 07:12 PM. Reason: No longer need answer

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,672

    Re: Adding new row to the top of a table using macro button?

    When you copy the data from the top row, are you copying only from column F to the last used column? If so, what is the last column used? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    11-10-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Adding new row to the top of a table using macro button?

    Okay, so for specifics:

    I'd like to, ideally, add a new row to the table between row 5 and 6 when clicking the macro button if that's possible.

    If it's not possible to add a new row there, then add a new row after row 20.

    Then, select B6-B19, C6-C19, D6-D19, E6-E19, F6-F19, and G6-G19 and shift those cells down 1.

    Copy the data from F7 and G7 and paste into F6 and G7.

    I hope that makes it more clear.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adding macro button: which option?
    By terrypin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-05-2020, 05:53 PM
  2. Adding a button to a simple macro
    By excelnewb2 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-08-2019, 05:56 PM
  3. [SOLVED] Copy table column "and" macro button over to right with each push of macro button
    By xlyfe in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-07-2018, 04:24 PM
  4. Adding a macro button
    By JonnyEnglish in forum Excel General
    Replies: 1
    Last Post: 01-29-2017, 08:07 PM
  5. Adding/removing the delete button automatically to a database in table
    By dmbroekhuysen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2014, 10:59 AM
  6. Adding a sorting button to a table to sort as desired
    By mj_bowen in forum Excel General
    Replies: 1
    Last Post: 06-10-2012, 11:37 AM
  7. Adding a macro button
    By Huwbob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2007, 05:05 AM

Tags for this Thread

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