+ Reply to Thread
Results 1 to 20 of 20

How to create a macro to pull data from one sheet to another based on cell information

  1. #1
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    How to create a macro to pull data from one sheet to another based on cell information

    Hello-

    I am creating a workbook where I have columns : First Name ; Last Name ; City; State ; Availability; Resume Sent to... ; Practice Group; Industry ; Additional Specialties ; Profile written? ; IFM complete?; Skyped or met?; Paperwork signed? ; References complete? ; Permission to market resume? ; Permission to use photo? ; Video introduction complete?

    I would like to be able to create a macro that pulls the entries that have specific data in the practice group column (i.e Government ; Corporate/Securities; etc.) to another worksheet. And I want the headers in row 1 to go as well.

    I have a little knowledge on code, but Im stuck.

    If someone could give me the code to work for just rows with "Government" in the practice group column (I) to to be copied to another sheet and open that sheet when the macro is run. I should be able to switch the different names where needed to make a macro for each one.

    Any help is well appreciated. Thanks guys.

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Do you have the sheets for Government and the others already created? Or do they need to be created by the macro if required?

    Do you want the data to be copy pasted or cut pasted?

    Incase its copy pasted, when new data is entered, do you clear the main file before putting in the new data or do you put the new data in the next available row of the main file?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    I want the macro to put the info into sheet two.

    Background: I have the sheet linked to an online spreadsheet. Everytime the spreadsheet is opened, it populates whatever data is in the spreadsheet online.

    So no one will be entering or altering the data in this spreadsheet.

    I want it copy pasted. leaving the general sheet having ALL the info, and a new sheet opens with only the data containing the criteria (i.e. Government) in the practice group (I) column. \

    Does this answer your question?

    Ultimately I will take the macros and make buttons for each one, so when the user opens the file it will be a sheet with buttons, and when they click which info they want it will take them to that new sheet. I don't need the codes for that (At least I think I should be able to do it) I'm just lost on how to get this particular macro to work.

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    So you want one macro for each sheet? Like one for Government, one for securities, etc? How about having all the sheets updated in one macro itself?

    Also, each time you update fresh data in the main file and the other sheets are updated, is it fine if the other sheets are cleared and then re-updated? This needs to be done to ensure duplicates are not present in the sheets.

  5. #5
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    All in one macro would be great, but I don't know if it's needed. As far as the other sheets being cleared and re-populated, that's fine.

    Apologies if I'm not being clear enough.

    I don't want/need the sheets to already exist when the file is opened.

    If possible, Id like for when the sheet is opened there are two sheets existing. 1. Splash page (containing the buttons that will navigate user) 2. the main page containing all info pulled from online.

    For instance, if user clicks the "Government" button, I want a new sheet, "sheet 3" to be created with the rows of info that contain Government, in column (I) Practice Groups.

    I just need the code for the macro to do this. I will then take the macro, link it to a button and thats that .. I think?!?!

    Now when the user goes back to home (splash) screen andclicks another button, say " Corporate/Securities" for instance then a new sheet will activate with the info for that.

    Would sheets 1 - 15 or how many ever dif. choices I have already have to exist or can the code just create a new sheet?

  6. #6
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    So is this possible?

  7. #7
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Would sheets 1 - 15 or how many ever dif. choices I have already have to exist or can the code just create a new sheet?
    No, it does not need to exist. The macro can create a new one for you on the spot.

    The reason i suggested that you can have all the sheets created - i thought that you didnt know its possible to have all the sheets done at one shot.

    Should the data be cleared from the main sheet after its copied or should it only be displayed on the new sheet while retaining it in the main sheet?

  8. #8
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    It should only be displayed on the new sheet while retaining it in the main sheet.

    Thanks sooo much.

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button

  10. #10
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    I am going to try it out and see how it works. Please give me a few hours as Im swamped at work. Just wanted to let you know I'll respond a little later on.
    Thanks.

  11. #11
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    I get a run-time error '9'
    "Subscript out of range" error message, and the debug lhighlighter starts on bolded line below.

    So you know My first row starts at C in my main worksheet. I had to hide columns a and B, but the first row has my headers C - S.


    Please Login or Register  to view this content.
    Last edited by arlu1201; 05-08-2013 at 01:27 PM.

  12. #12
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    First of all,

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

    A subscript out of range occurs when the sheet name in the code does not match the sheet name in the file. Check that both your sheets are named as Main and Government. If you have some other names, you can change it in the code to reflect the sheets in your file.

  13. #13
    Registered User
    Join Date
    05-08-2013
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Your problem can also be solved successfully through Pivot Tables and
    hyperlink.
    I will help you, if required.

  14. #14
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Sorry about that.

    As far as the sheets. My "main" sheet is named Main, as you have it in your code. The government sheet does pop up once I hit the button, but there is nothing that populates the sheet, not even the headers.

  15. #15
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    You said that all your data starts from C, so change all the column references from A to C in your code.

  16. #16
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    For some reason the code still isnt working.

    I found out how to create the proper pivot tables I need for each factor. Is there a macro I can create that creats the pivot table when I run it.?

  17. #17
    Registered User
    Join Date
    12-15-2009
    Location
    USA
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    27

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Advanced Filter?

  18. #18
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    When you say the code doesnt work, are you getting an error message?

  19. #19
    Registered User
    Join Date
    05-03-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Yes I am getting a error code 9.

    Thank you for all of your help btw.

  20. #20
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: How to create a macro to pull data from one sheet to another based on cell information

    Error code 9 pertains to subscript out of range. Check the sheet names given in the code v/s the sheet names in the file. Ensure both match and you will not get that error.

+ 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