+ Reply to Thread
Results 1 to 14 of 14

Automatically auto-fill cells, when inserting new row

  1. #1
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Post Automatically auto-fill cells, when inserting new row

    Say I have two Sheets in a workbook. A and B. Rows from SheetA are used in formulas in Sheet B. When I insert a new row in SheetA, the formulas automatically change in Sheet B, and a row is added. The thing is that I have to drag and fill, the new row everytime, as it is empty. In reality, I have about 10 Sheets connected to SheetA, and I have to drag and fill the code for every single sheet everytime. Is there a way for excel to do this automatically?

    Thank you very much in advance!

    Allerdrengen
    Last edited by Allerdrengen; 12-01-2017 at 09:31 AM.

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Automatically auto-fill cells, when inserting new row

    You can do it via VBA or Tables.

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Re: Automatically auto-fill cells, when inserting new row

    Thank you for your response!

    I have now uploaded a before and after workbook. In the after workbook under "Data", I have added a new row (19). I have then inputtet my desired data. The sheets "May", "June" and "July" get their data from the "Data" sheet. The problem is that upon adding the new row, the functions doesnt apply for the new row and I have to manually click and drag the data for all of the different sheets for the new row. I have to do this for the data in the columns A-C, but also from AM-AX(which gets it data from the sheets itself). The after workbook is how it should look upon adding the new row and inputting my desired data in in the "Data" tab. I would like the other sheets to adapt to this data. Is this possible?

    Thank you so much in advance.
    Attached Files Attached Files

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Automatically auto-fill cells, when inserting new row

    I am sure that someone has a more specific/better idea, but what I would do now it would be to set the data in the adjacent sheets, all except the main sheet as tables including some blanks(like A1:AX100 as the table range) and modify my formulas adding an if
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Etc.

  5. #5
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Re: Automatically auto-fill cells, when inserting new row

    Thank you for your response!

    However, if I use that method, the new row in the adjacent sheets is still blank. Any suggestions?

    Thanks

  6. #6
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Automatically auto-fill cells, when inserting new row

    New row in yellow, found marked in yellow in June sheet as well.

    I've changed the formulas only for June sheet.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Re: Automatically auto-fill cells, when inserting new row

    Thanks again for your reponse. I guess it works if you only insert new rows in the end of the sheet, but often I'll have to insert new rows in between, like in my example, and then the code wont follow. But thanks anyways!

  8. #8
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Re: Automatically auto-fill cells, when inserting new row

    Anyone got an idea?

  9. #9
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Automatically auto-fill cells, when inserting new row

    This newly added row between your data should appear on all tabs or only on the one matching the date?

  10. #10
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Re: Automatically auto-fill cells, when inserting new row

    As a new row is added to the "data" tab, it should appear in the same row number on all "month" tabs and the code should preferably appear by itself. In my real sheet, i have about 20 "month" tabs, that's why I'd rather not click and drag all the code, for all the tabs.

  11. #11
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Automatically auto-fill cells, when inserting new row

    Assuming all your sheets are contiguous, then click on the first sheet,hold down SHIFT and click on the last sheet. (If they are not contiguous you will have to hold down CTRL instead of SHIFT and click on each one). Then you can highlight the row in the visible sheet and Insert | Row - this will be inserted at that position in every sheet which is part of the group.
    To ungroup the sheets right-click on one of the sheet tabs.
    Hope this helps.

  12. #12
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Re: Automatically auto-fill cells, when inserting new row

    I already do that, but the problem is not inserting the rows, the problem is that the code doesn't apply automatically to the new rows. The rows just turn up blank. Any suggestions?

    Thanks

  13. #13
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Automatically auto-fill cells, when inserting new row

    Try this:

    Please Login or Register  to view this content.
    In the part below add your Sheets and ranges.
    Please Login or Register  to view this content.
    Change your formulas as in post #4

    I am not familiar with VBA and I am currently trying to learn it and I am 100% sure that someone else has a more efficient approach to this.
    Attached Files Attached Files
    Last edited by PaulM100; 11-30-2017 at 10:54 AM.

  14. #14
    Registered User
    Join Date
    04-03-2017
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    90

    Re: Automatically auto-fill cells, when inserting new row

    Hi PaulM100

    Actually the code you first wrote, even though simple, did the trick for me. Just with a bit of tweaking!
    Thank you for your help! Repped

+ 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. Replies: 6
    Last Post: 05-01-2014, 06:04 PM
  2. Auto inserting data in cells
    By Davycc in forum Excel General
    Replies: 4
    Last Post: 09-09-2013, 02:32 PM
  3. Formula to auto update cells below when inserting a new row
    By onpoint in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-13-2013, 04:20 PM
  4. [SOLVED] automatically fill cells because other cells have been filled - SEE OPENING POST
    By tlacloche in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-02-2013, 10:38 AM
  5. Replies: 28
    Last Post: 02-03-2012, 05:12 AM
  6. Replies: 1
    Last Post: 09-07-2011, 05:57 PM
  7. Auto Inserting Copied Cells
    By whatsmyname in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-26-2010, 10:50 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