none
Excel VBA to modify worksheet rows and data RRS feed

  • Question

  • I am working on a small project and would like to try something.I

    have four sheets in the workbook. Sheet1 to Sheet3 contains a list of items with values assigned to each item. These lists will be added to regularly. Sheet4 is a summary of the other three worksheet data.

    For example rows 1 to 50 of sheet4 may contain summary of the list in sheet1, while rows 51 to 100 will have list of items from Sheet3, and so on.

    I would like to have any new row of data from sheet1 inserted just after the last record from sheet1 and before the first record from sheet2.

    Is this clear enough? I would like any help I can get.

    Thanks


    Peter Oyewole

    Thursday, November 28, 2019 10:04 PM

All replies

  • Hi Peter,

    I would like to confirm:
    All of Sheet1 to Sheet3 have the same number of rows? i.e. If Sheet1 has 60 rows, Sheet2 and Sheet3 have the same 60 rows?
    And I hope you will provide the code.

    Regards,


    Ashidacchi -- http://hokusosha.com

    Friday, November 29, 2019 12:16 AM
  • Hi Peter,

    Considering that your question is mainly related to using VBA in Excel, to help you better solve the problem, I will move the thread to Excel for Developers forum. You may get more helpful replies there.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Friday, November 29, 2019 2:07 AM
  • Thanks. I really appreciate that.

    Peter Oyewole

    Friday, November 29, 2019 5:27 PM
  • No. The sheets 1 to 3 do not have the same number of rows.

    Peter Oyewole

    Friday, November 29, 2019 5:28 PM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Can be done with PQ alone, using Append(),
    but recommend using PQ and PP together.
    Share your file for your unique ontogenesis.
    http://www.mediafire.com/file/nq3zsz4nzkokrx3/11_29_19a.xlsx
    http://www.mediafire.com/file/vns2zovrqxcmvxr/11_29_19a.pdf/file

    Saturday, November 30, 2019 1:03 AM
  • Hi Peter,

    Thank you for the information. And I would like to know more detail about your issue.
    Could you share your file (with four sheets in it) via cloud storage such as OneDrive, Dropbox, etc?
    (Remember to modify/edit the vital/personal data before sharing.)

    Regards,

    Ashidacchi -- http://hokusosha.com

    Saturday, November 30, 2019 3:51 AM
  • Hi Peter,

    Can't you share your file?
    If so, you need to provide more detailed or clearer explanation.
    e.g.
    Where should we begin to insert rows in Sheet4 (what is the row number)?
    How many rows should we insert (how to know the number of rows)?

    Regards, 

    Ashidacchi -- http://hokusosha.com

    Monday, December 2, 2019 2:44 AM