+ Reply to Thread
Results 1 to 17 of 17

automatically copy data from different sheets and sequentially copy to another sheet

  1. #1
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    automatically copy data from different sheets and sequentially copy to another sheet

    greetings all,

    i have a problem that i hope you can help me with. i have a workbook with 4 sheets. On three of the sheets, there are products and prices that could be entered into rows on sheet7, sheet5, sheet4 as noted below. data will always be inputted from top to bottom on these rows.



    Data inputted onto sheet7, sheet5 or sheet4 needs to copy over onto sheet2 starting at sheet2 - L2:M2 and then subsequent entries will advance to the next row.

    Data will be inputted onto these three sheets at specified rows starting with top row and incremening down. there is a possiblily that all three sheets will have data inputted into the specified areas at any given time. Each sheet will have 15 rows and each row will have two columns of data.

    Sheet7 L31:M45 copied data will not be cleared

    Sheet5 E5:F19 copied data will not be cleared

    Sheet4 L31:M45 copied data will not be cleared



    Data needs to be copied over to the following sheet:
    Sheet2 L2:M16

    there will never be a need for more than 15 rows that will need to be copied over to sheet2

    I have attached a sample worksheet to illustrate.

    thanks in advance for you time and assistance!
    Last edited by darbar76528; 10-02-2017 at 01:12 PM.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    it must be a vba ?
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    not if there is a better way

  4. #4
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    I just thought that the only way to solve this would involve vba with XlUp in order to stack the products from the three sheets to the one sheet

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    You've Excel 2016 so why don't use builtin feature like PowerQuery (Get&Transform) ?

    here is example

  6. #6
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    this does do the job, however, is there any way to auto refresh the data. I cannot be sure that my employees will remember to refresh each time...

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    Yes of course. But I need to find a little (unfortunately) vba code to refresh connection

  8. #8
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    I have no problem with vba. I thought of using this process earlier, but I could not find any code to auto refresh the data

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    You need refresh data after change something in source or enough to refresh on open workbook ?

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    If on workbook open

    refreshpq-onopenworkbook.jpg

    if on worksheet change, here is:
    Please Login or Register  to view this content.
    Last edited by sandy666; 10-01-2017 at 12:24 AM. Reason: file attached

  11. #11
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    Outstanding! thanks for all your help. I will mark this as solved.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    If you did it - ignore it.
    Thank you.

  13. #13
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    greetings sandy666,

    upon inserting this solution into my workbook, I found that it has an unintended flaw. on my worksheet, I have a checkbox application vba running that, once a checkbox is checked, it sends the two columns which contain the product and price over to the totals field(power query assigned table area). when I implement the power query solution, the text avoids copying over to the query assigned area and is inserted just below the power query assigned table area. is there a workaround that you are aware of or is a vba solution the only way to go? Just looking for some professional advice. thanks!

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    If I understood correctly you need total under result query table. Right click on this table, ten: Table - Totals Row - and select whatever you want
    if I misunderstood - I can't help - I am away from VBA, sorry.

    One example is worth a thousand words

    maybe add zeroes to your source tables to get full size result table then define whatever you want with your vba
    Last edited by sandy666; 10-01-2017 at 04:59 PM.

  15. #15
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Automatically copy data from different sheets and sequentially copy to another sheet

    greetings all,
    i have a problem that i hope you can help me with. i have a workbook with 4 sheets. On three of the sheets, there are products and prices that could be entered into rows on sheet7, sheet5, sheet4 as noted below. data will always be inputted from top to bottom on these rows.

    Data inputted onto sheet7 (Build Custom Carport), sheet5 (Red Barn Workup - Options) or sheet4 (Wooden Bldg) needs to automatically copy over onto sheet2 (Prices and RTO) starting at* L2:M2 and then subsequent entries will advance to the next row down thru row 23.
    Data will be inputted onto these three sheets at specified rows starting with top row and incremening down. there is a possiblily that all three sheets will have data inputted into the specified areas at any given time. Each sheet will have 20 rows and each row will have two columns of data.
    Sheet7 L31:M50 copied data will not be cleared and as products appear, they will need to automatically copy over to Sheet 2
    Sheet5 E5:F24 copied data will not be cleared and as products appear, they will need to automatically copy over to Sheet 2
    Sheet4 L31:M50 copied data will not be cleared and as products appear, they will need to automatically copy over to Sheet 2

    Data needs to be copied over to the following sheet:
    Sheet2 L2:M23

    I have attached a sample worksheet to illustrate.
    thanks in advance for you time and assistance!
    P.S.: I have attempted to use power query with no success.* it does not perform correctly due to the checkbox vba application.* The copied data will not copy into the named table as it should.* Instead, the data copy\paste begins just below the active table....I was hoping for a vba solution unless someone has a better idea.
    Last edited by darbar76528; 10-02-2017 at 01:11 PM.

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    I think you forgot unprotect some things in your workbook
    Last edited by sandy666; 10-02-2017 at 01:52 AM.

  17. #17
    Registered User
    Join Date
    07-30-2017
    Location
    texas
    MS-Off Ver
    2016
    Posts
    99

    Re: automatically copy data from different sheets and sequentially copy to another sheet

    You're right, Thanks so much for the help!

+ 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] automatically copy input data to different sheets
    By qiyusi in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-04-2017, 10:25 PM
  2. Copy and Paste data automatically from master sheet to other sheets
    By wschleis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2014, 04:29 PM
  3. [SOLVED] Automatically Copy Data from Multiple Sheets to a Master Sheet
    By Ryan_Brandt in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-10-2014, 06:45 AM
  4. [SOLVED] Help with VBA code to copy and rename a sheet sequentially
    By niftysquirrel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-03-2013, 09:54 AM
  5. Replies: 5
    Last Post: 04-18-2013, 07:26 AM
  6. Copy sequentially down list to every other row in another sheet
    By eonizuka in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-23-2010, 06:58 PM
  7. Copy data from 1 sheet to other sheets automatically
    By mattyb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2008, 03:43 PM

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