+ Reply to Thread
Results 1 to 15 of 15

Macro to pull information from one tab to another?

  1. #1
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Macro to pull information from one tab to another?

    Hey Everyone,
    I am trying to put together a macro to pull data from one tab to another. The key here is that I want it to take the entire row.

    Basically it will look something like this:

    As someone goes down the rows of the checklist putting x's in the boxes that need one (there is a column set aside for that), that box will turn red.

    That part is easy and done.

    What I cant figure out how to do next is to automatically have a copy made of the row with the red cell and send them to another document.

    Anyone have any ideas???

    Thanks for the help!

    Aaron

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Macro to pull information from one tab to another?

    As someone goes down the rows of the checklist putting x's in the boxes that need one (there is a column set aside for that), that box will turn red.
    I assume you are not using check box controls, but a cell as a "box". If so, here is basic code to get you started. It assumes column-C as the "X" column and copies the row from Sheet1 to sheet2.

    Adjust sheet and range references as needed or post a sample workbook for more help.
    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    Hey Palmetto,

    Thanks for the advice!

    Glad to post an example. New to this forum though and not sure how to do it other than posting as an attachment. Can I email it to you? And explain a little further?

    Thanks!
    Aaron

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Macro to pull information from one tab to another?

    To add an attachment, when posting a new thread, scroll down the page until you see the section titled "Additional Options", then click the "Manage Attachments" button - should be intuitive from there.

  5. #5
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    Hey Palmetto,

    Here is the document... checklist ex.zip

    Let me know what you think. You will see that the column with the red x is column 4 and what I ultimately want to have happen is, after going through the document the rows that have a red box, I would like sent over to the next tab on the sheet.

    What do you think?
    Thanks,
    Aaron

  6. #6
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Macro to pull information from one tab to another?

    Delete the existing code in the sheet module and copy this into it.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    Palmetto,
    Call me crazy and totally ignorant but when I am trying it its not working...

    Im sorry... Can you give me a bit more direction?

    Thanks for all your help!

    Aaron

  8. #8
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    Scratch that. I got it working but there are a couple of things that I still cant quite figure out:

    1) It doesn't appear to be pulling all the data (there is more that is grouped and hidden on the right side

    2) Its pulling the data as soon as I make the X which means if there is a mistake made you have to manually go in and delete that line every time. The way I almost envisioned it was to have a little button on the bottom that when you hit GO it starts up the macro and pulls the entire rows.

    Also, cosmetically, I wondered if there was a way to automatically pull the header as well when that "Go Button" is hit.

    I guess I am almost looking to recreate the look and feel of the first tab but only with the relevant rows.

    What do you think?

  9. #9
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Macro to pull information from one tab to another?

    1) It doesn't appear to be pulling all the data (there is more that is grouped and hidden on the right side
    It is copying the entire row per my testing, including when grouping is collapsed.

    2) Its pulling the data as soon as I make the X which means if there is a mistake made you have to manually go in and delete that line every time.
    This is what you asked for per your initial post.

    The way I almost envisioned it was to have a little button on the bottom that when you hit GO it starts up the macro and pulls the entire rows.
    How about using a double-click in the "X" column? Code below.
    Please Login or Register  to view this content.
    Also, cosmetically, I wondered if there was a way to automatically pull the header as well when that "Go Button" is hit.
    Just manually copy the one row to the target sheet. Once it is there, no need to copy each time.

    See attached.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    The double click idea is great. But I am envisioning a button at the bottom of the checklist so after I have gone through the entire list, I can hit that button and it will run the entire macro at once (so they don't all have to be done individually and it also wont send information until I am SURE that it is the correct information I want to send over.)

    Also, the way it currently is seems to mess up the grouping a bit. Any way to keep that intact / automatically remove it when the information is transferred over.

    Thanks!

  11. #11
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Macro to pull information from one tab to another?

    OK . . . a button it is. This code automates Advanced Filter.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    WOW! Thats pretty awesome!

    Now if I wanted to play around with it (i.e. move this macro to another document/ change what the button looks like, etc), is that going to be a big pain in the butt?

    I guess what I am really asking is along the lines of give a man a fish / teach a man to fish... I would love to know how you did this (pardon my limited knowledge of excel :/ ) So I can try to do similar type of things...

    Thanks for all your help Palmetto!

  13. #13
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    Also, what do I have to do to include the first row? as well.

    This is really amazing! Thanks for your help!

  14. #14
    Registered User
    Join Date
    12-09-2009
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Macro to pull information from one tab to another?

    Hey Palmetto,

    Thanks for all your help before putting together this code.

    I was trying to recreate it for a new project I was working on using a similar idea but for some reason when I put the code in the new document, its not doing the same thing.

    Any advice on how to get it to work?

    Thank you,
    Aaron

  15. #15
    Registered User
    Join Date
    10-11-2011
    Location
    Burlington, VT
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Macro to pull information from one tab to another?

    this was great. Just curious how you code would change if you had multiple sheets that you wanted to look at and then consolidate only marked lines onto a single sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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