+ Reply to Thread
Results 1 to 48 of 48

Code to Pull Records From 2 Other Sheets

  1. #1
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Code to Pull Records From 2 Other Sheets

    Hello,

    Can someone come up with code to fill in cells from 2 other sheets('AFRsDB' & 'AFRsParts') based on cell F2 on my 'AFRsInput' sheet.

    Thank you in advance for your help,
    FF

  2. #2
    Registered User
    Join Date
    10-06-2007
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Code to Pull Records From 2 Other Sheets

    Hi - I have taken a look.

    Its unclear exactly what you are trying to achieve, however it does look like you can achieve what you need with if statements and vlookups.

    Can you be more descriptive on what you need.

    Cheers
    Scar1

  3. #3
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    I think I will need a macro to do this since the sheet will be for entry also thank you.

  4. #4
    Registered User
    Join Date
    10-06-2007
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: Code to Pull Records From 2 Other Sheets

    Are you pulling information based on the dropdown F2 value?

  5. #5
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Yes if you see the orange shape below with text information.

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Code to Pull Records From 2 Other Sheets

    You can pull data into the form without a macro, if that's all it is used for. However, if you're using the same form to add data to the other two tabs, you'll need VBA to retrieve records.

  7. #7
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Yes it will be for entering data also. Thank you

  8. #8
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Code to Pull Records From 2 Other Sheets

    The first procedure goes in the worksheet code module for Sheet AFRsInput.

    The second procedure goes in the standard code module1.

    When an entry is made in cell F2 it will trigger the first macro which then calls the second procedure. If the entry is valid the second procedure will fill in the data on the input sheet. If the entry is not valid, a message will appear and the procedure will terminate.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  9. #9
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    I get error by trying this sorry.

  10. #10
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Code to Pull Records From 2 Other Sheets

    The code is performing as it should. The problem is an anomoally in the worksheet that is causing vba to detect a merged cell condition. I tried running some routines to elimiunate any merged cells in the applicable ranges but that did not cure the problem and I cannot find where the cuprit is residing. Merged cells and vba don't mix well although it can usually be worked around. In this case, it is beyond my technical skills to locate and fix the cause of the code interruption. Sorry. Maybe someone else can come up with an idea that will work.
    Regards, JLG

  11. #11
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421
    Thanks for your efforts.
    Last edited by AliGW; 03-16-2019 at 07:03 AM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    To draw attention back to this thread, you should bump it by posting again to it. Please do NOT open new threads directing members to look at another thread of yours. The thread you opened to do this has been deleted. Thank you for your co-operation.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  13. #13
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    My apologies how do I bump it please? I will cooperate as you wish.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    You just have - as I said, you just need to post to the thread again. However, we don't expect you to do this too soon - if a day has elapsed since the last post to your thread, it's fine to bump.

  15. #15
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    I thought by having replies at first asking questions that I thought I mentioned in the original post lessons my chances of getting actual help. My fault for assuming. Have a wonderful day!

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    It does (but only because it is removed from the unanswered threads list), but then if helpers need clarification, they need clarification and need to answer. I think what you assumed was that you had given enough detail, when in fact you hadn't.

    Forum Rule #5 covers this: https://www.excelforum.com/forum-rul...rum-rules.html

  17. #17
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Did you look at the workbook? I had a shape on there describing the details.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    No - I don't do VBA, sorry. My posts here were to do with moderation issues. I am sure someone who does will take a look, but please remember that it is a weekend.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    Here's a solution using formulae (well, the first half - I'll post the rest later). It uses variants of this INDEX MATCH formula:

    =INDEX(Table2[[SO '#]:[Notes to Customer]],MATCH(AFRsInput!D$4,Table2[AFR '#],0),MATCH(AFRsInput!J13,Table2[[#Headers],[SO '#]:[Notes to Customer]],0))
    Last edited by AliGW; 03-16-2019 at 08:25 AM.

  20. #20
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Thank you AliGW for helping me but I apologize again I will need code because this sheet will also be for entering data.

    I really do appreciate your efforts.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    Two more formulae required to produce the table on the right:

    =IF([@Qty]="","",$F$2)

    =IFERROR(INDEX(Table4[Qty],AGGREGATE(15,6,ROW($1:$500)/(Table4[AFR '#]=$F$2),ROW(1:1))),"")

    Final non-VBA solution attached.
    Attached Files Attached Files

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    OK - well never mind, than. You'll have to wait for a coder.

  23. #23
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Code to Pull Records From 2 Other Sheets

    If the idea is to pull data from sheets 2 and 3 to sheet 1, amend it then send the updates back then I can help with that.

    I just need an explanation of what the table in columns N:Q is for and how it will be filled out. i.e. will it only ever have 3 entries per AFR# or could this vary?

    BSB

  24. #24
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Thank you for help in advance. I updated the workbook without any code.

    The AFRsInput sheet will be for looking up records and also for inputting new records. If you look at the shape in orange it explains the ranges needed.

    The AFR# is the control ID for the records.

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    Which bit of this does my solution in post #21 not do?

    By selecting AFR #
    1) D4:D18 & H4:H18 & L4,L7,L10,L13,L19 will fill in data from 'AFRsDB' sheet based on the AFR # selected in F2.
    2) N3:Q3 and down will fill in parts from 'AFRsParts' sheet based on the AFR# selected in F2.
    If there is more to it, as you suggested above (i.e. input), then you need to clarify.

  26. #26
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Code to Pull Records From 2 Other Sheets

    Quote Originally Posted by fredfarmer View Post
    If you look at the shape in orange it explains the ranges needed.
    I understand that bit. What I don't understand is the the "Parts" table, hence the clarification questions above.

    BSB

  27. #27
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    AliGW your formulas work perfectly if this was just a reference sheet. I may consider doing that and have another sheet for input. I would prefer having one sheet to do both though.

    You are very kind for helping.

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    So, where and what are the inputs going to be? This bit is not at all clear in your sample workbook. You need to give us the whole picture. We can only provide solutions based on what you are showing us that you want.

  29. #29
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    BSB,

    The parts table is for parts that are used for each AFR kind of like a repair order at a shop.

  30. #30
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    The inputs will be all the ranges in the orange shape. Which will be the same ranges that your formulas cover.

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    Sorry, but that's not what you said.

    In the orange box you tell us that you wish to select a number in F2 and from that automatically populate all those cells mentioned. So, explain in words how you really do want it to work.

  32. #32
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    The cell F2 will be for selecting previous AFRs and fill out all of the ranges like your formulas do very well.

    I would like to also have the ability to create a new AFR. Have a macro to create a new AFR which will clear all of the ranges and manually fill them out. Then once they are filled out click another shape to save that AFR.

    Thank you

  33. #33
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Code to Pull Records From 2 Other Sheets

    Thanks - now it's clear what you want. It would have helped if you had said this clearly right at the start!

    I'll leave it to BSB.

  34. #34
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    I truly apologize for my ignorance. You have been most helpful.

  35. #35
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Code to Pull Records From 2 Other Sheets

    Apologies for the delay (hectic day!)

    Give the attached a try for starters.
    It will recall the data when you change the AFR# in F2 and you can amend as you see fit.
    There's a button at the top of Column L that when clicked will update any changes you've made to the table on sheets AFRsDB.

    I'm still none the wiser to how you use the Parts table so although it will populate when you change the AFR I've not added any code to make that stamp data to the part sheet (yet).

    Anywhere near what you need?

    This whole things would be easier (and look nicer) is it was done using a UserForm

    BSB
    Attached Files Attached Files

  36. #36
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Code to Pull Records From 2 Other Sheets

    Also the ability to add new rows to the bottom of the table still needs some work as you have it set up not to allow anything but the validation list in F2.
    So how do you generate a new AFR?

    BSB

  37. #37
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    I would like a code to 'Add' new AFR which will clear the ranges and then start to enter the number in D4 then fill out the rest of the fields. When satisfied have another code to 'Update' the information which will save to both 'AFRsDB' and 'AFRsParts' sheets.

  38. #38
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Code to Pull Records From 2 Other Sheets

    OK, I have to pop to the shops but will be back in an hour and will work on that then.

    Does it do what you need so far?

    BSB

  39. #39
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Oh I didn't see your latest update. That works great thank you. Is it possible to have the Add New also?

  40. #40
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    I also have to go out for a while normal Saturday errands. I appreciate everything and will return later.

  41. #41
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Code to Pull Records From 2 Other Sheets

    The attached version now has an "add new" button that when clicked will add a new row to the DB sheet and increment the AFR# by 1 based on the highest in there so far.
    I presume that will work for you but can revisit if these numbers should be derived in a different way.

    Need more info on the parts table. Easy enough when it's a new record being entered as it will (it doesn't yet!) stamp anything in that table to the bottom of the data on the third sheet.
    But what if you're amending a record, would you amend those existing figures? Would you add new rows to that table?
    All important things to know before I can write the code..

    BSB
    Attached Files Attached Files

  42. #42
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Code to Pull Records From 2 Other Sheets

    I think part of the problem is that your data is all in tables and the code I wrote does not address tables. I don't have enough experience working with tables to fix the code so it will do the same thing with tables that it does with untabled data. Some of the code works fine, but then it hits an area where the sytax will not execute properly because of the way the tables are seen by vba.

    I see that @BadlySpelledBuoy has already started with the proper code for use with tables.
    Last edited by JLGWhiz; 03-16-2019 at 02:24 PM.

  43. #43
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Conditions of Parts Table:

    1) Able to add new parts to new record
    2) Able to delete parts and have the AFRsParts sheet update
    3) Able to update parts to AFRsParts and AFRsDB with Update button

    Thank you

  44. #44
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Hi BSB,

    Almost everything is working at this point. The Parts aren't updating on the 'AFRsParts' sheet though when adding new records.

    Thank you so much,
    FF

  45. #45
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Code to Pull Records From 2 Other Sheets

    Good evening FF,

    Perhaps this version will do all you need?
    At first I had it updating the parts list by looping through and overwriting, but then I remembered you need to delete too so changed it so that it just deletes any parts list for the given AFR and then adds them to the list again.
    This means if you've deleted any from the parts list on Sheet 1 then it will also get deleted from Sheet 3.

    Give me a shout if I've missed anything or if it's not doing quite what you need.

    BSB
    Attached Files Attached Files

  46. #46
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Hello BSB,

    I selected the 'Add New' which cleared and made ready for new entries which works fine. Then I started to add a new AFR 10010 in D4 and filled out the rest of the fields but when selecting 'Update Data' it didn't paste the data to the 'AFRsDB' but it does paste to the 'AFRsParts'. Though it will paste the same info there ('AFRsParts') as many times as the button is selected.


    One request also if possible would be to insert the AFR number in Parts Table when entering parts. I want to hide that column eventually.

    I appreciate it very much.
    Last edited by fredfarmer; 03-17-2019 at 06:45 PM.

  47. #47
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    Hello BSB,

    I was wondering if it's more logical to have one cell (D4) on the 'AFRsInput' sheet for the AFR #. It seems that it is prone to errors having the same value in multiple places.

    Could it still be a list and if the number isn't there prompt: "Would you like to add a new AFR?"

    What do you think?

    Also the 'Add New' is set to increment to the next number. Could this be open to allow the user to put a number that is not already in the list?

    Thanks so much again
    Last edited by fredfarmer; 03-19-2019 at 09:29 AM.

  48. #48
    Forum Contributor
    Join Date
    03-12-2019
    Location
    USA
    MS-Off Ver
    2010
    Posts
    421

    Re: Code to Pull Records From 2 Other Sheets

    please bump to front thank you

+ 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. VBA code to pull data between sheets on a condition of column K
    By MichelleD75 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2016, 03:06 PM
  2. Code to manage records in multiple sheets
    By GeorgeT in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-03-2014, 02:29 AM
  3. [SOLVED] Help to pull records from spreadsheet
    By rwalker1052 in forum Excel General
    Replies: 8
    Last Post: 11-08-2013, 05:21 PM
  4. Macro code to pull the data from different sheets to main sheet
    By Shanthuday in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2012, 09:54 AM
  5. Macro code to pull data from different sheets based on the header of the main sheet
    By Shanthuday in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-21-2012, 05:00 AM
  6. Is there a way for my code to pull from different sheets??
    By confusedvba in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2010, 11:23 AM
  7. Pull data from multi-row records
    By thermometer98 in forum Excel General
    Replies: 2
    Last Post: 02-21-2006, 12:00 PM

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