Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

Thread: How to Copy From Multiple Sheets Based On 1 Sheets Input

  1. #1
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013

    How to Copy From Multiple Sheets Based On 1 Sheets Input



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Sorry in advance for the confusing description, I couldn't think of and easier way to type this.

    I'm in need of help regarding copying and pasting data from 2 different data sheets to one "filtered data" sheet based on the inputs of a fourth sheet called "Input"

    Currently I have a query setup to the "data" sheet. Every time I download the most recent data entries from a server and save, it will actively change the Inputs on the "data" sheet. From there I go to the "Input" sheet and type the top priority numbers so it will filter the desired data from "data" and "data 2" sheets to the "filtered data" sheet.

    Attached are a before and after example of what I'm looking form. Is this possible?

    Thank you for any responses in advance.


    Start.xlsx Finish.xlsx



    Mr.Excel Link:
    https://www.mrexcel.com/board/thread...input.1119689/
    Last edited by Pahickham; 2020-01-06 at 08:35 PM.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    In the attached, enter your search criteria in any of the cells A2:A17 on the Input sheet and click the button.

    It looks for the presence of the criteria you enter anywhere in the description column, so if you just enter wor you will get all the rows with working in the description. This behaviour can be tweaked if you want.
    Attached Files Attached Files
    Last edited by p45cal; 2020-01-11 at 01:06 PM.

  3. #3
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    Sorry for the late response.

    Your attachment worked perfectly. However, I was hoping you could help me with the final edit. Unfortunately, some things needed to be changed.

    The "Data" sheet is a dynamic sheet that info can be changed weekly.

    The goal on this sheet is like before. Copy and paste from "Data" to "Filtered Data" based on the value in "Input"
    The changes are there's no "Data 2" sheet now and if a value has been typed on "Filtered Data" in the manually inputted columns then I'd like for it to stay with that row each time the filter button is clicked, as long as that input number is still active, otherwise delete the entire row.

    Also added 4 more sheets:
    - the data in "Section 1" is based on the "Data" sheet anytime column B says 1 then copy and paste that data to "Section 1" it will also have manually inputted data that i would like to keep as long as that info is on the "Data" sheet after the next update.
    - the data in "Section 2" is based on the "Data" sheet anytime column B says 2 then copy and paste that data to "Section 2" it will also have manually inputted data that i would like to keep if that info is on the "Data" sheet after the next update.
    - the data in "Section 3" is based on the "Data" sheet anytime column B says 3 then copy and paste that data to "Section 3" it will also have manually inputted data that i would like to keep if that info is on the "Data" sheet after the next update.
    - the data in "Section 4" is based on the "Data" sheet anytime column B says 4 then copy and paste that data to "Section 4" it will also have manually inputted data that i would like to keep if that info is on the "Data" sheet after the next update.

    Reference:
    Start.xlsm Finish.xlsm

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Pahickham View Post
    Copy and paste from "Data" to "Filtered Data" based on the value in "Input"
    The changes are there's no "Data 2" sheet now and if a value has been typed on "Filtered Data" in the manually inputted columns then I'd like for it to stay with that row each time the filter button is clicked, as long as that input number is still active, otherwise delete the entire row.
    What determines if it's still active; that it's on the Data sheet or that it's on the Input sheet?

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Pahickham View Post
    Also added 4 more sheets:
    - the data in "Section 1" is based on the "Data" sheet anytime column B says 1 then copy and paste that data to "Section 1" it will also have manually inputted data that i would like to keep as long as that info is on the "Data" sheet after the next update.
    With these four sheets, is whether the info is on the Data sheet determined by a match in all columns A to I? (If you say yes to this, that row on the Section n sheet will be deleted if there's a difference in just one of those columns.)

  6. #6
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    The "Data" sheet is where all of the information comes from. The "Input" sheet only determines the "Filtered Data" sheet. The Section sheets are pulling from the "Data" sheet. For the Section sheets i need the full row to be copy and pasted over, it's column B that determines what gets transferred over. If column B of of "Data" = 1 copy and paste to "Section 1", if column B = 2 copy and paste to "Section 2", etc.

    Not sure how you would approach this, but a thought of mine was for the first macro to copy and paste from the manually input columns to the "Data" sheet so that when the next macro copy and pastes the specified data over it contains whatever was typed by the user. Might be an easier way though.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Pahickham View Post
    The "Data" sheet is where all of the information comes from. The "Input" sheet only determines the "Filtered Data" sheet.
    Understood.



    Quote Originally Posted by Pahickham View Post
    The Section sheets are pulling from the "Data" sheet. For the Section sheets i need the full row to be copy and pasted over, it's column B that determines what gets transferred over. If column B of of "Data" = 1 copy and paste to "Section 1", if column B = 2 copy and paste to "Section 2", etc.
    Yes, but you added the proviso: as long as that info is on the "Data" sheet, and I need to know what decides if that info (row) is on the Data sheet; is it a match on all columns A to I? (If you say yes to this, that row on the Section n sheet will be deleted if there's a difference in just one of those columns.)



    Quote Originally Posted by Pahickham View Post
    Not sure how you would approach this, but a thought of mine was for the first macro to copy and paste from the manually input columns to the "Data" sheet so that when the next macro copy and pastes the specified data over it contains whatever was typed by the user. Might be an easier way though.
    I'll sort that out, don't worry.

  8. #8
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    Understood, column E can be used to confirm whether it's still on the Data sheet. If the "Orders" match then keep it.

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    Attached.
    Attached Files Attached Files
    Last edited by p45cal; 2020-01-24 at 10:51 PM.

  10. #10
    Acolyte Pahickham's Avatar
    Join Date
    Jun 2017
    Posts
    25
    Articles
    0
    Excel Version
    2013
    Thank you that appeared to do the trick. Couple of quick questions:
    1. If i manually clear the "section" sheets then run the filter it creates an error. Was just curious as to why that happens and if that can be edited to allow me to clear it by hand and it still run?
    2. Out of curiosity why's the range for "Input" sheet set for column C when the numbers are in A. It works, but I was just curious didn't see an offset or anything in there.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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