+ Reply to Thread
Results 1 to 10 of 10

Userform to find specfic data with certain criterias and then copy to another workbook

  1. #1
    Registered User
    Join Date
    11-06-2014
    Location
    UK
    MS-Off Ver
    Office 2003
    Posts
    10

    Userform to find specfic data with certain criterias and then copy to another workbook

    Hello

    Windows Server 2005
    Excel 2003

    First post (hello everyone )

    I've only been using VBA properly for about a month now so all of this is new to me and been using it for work.

    I've created two spreadsheets one with data (testregister) and one with headings (testmailout). When a user closes testregister a userform will pop up. The userform has a DTPicker, a combo box with a list of customers and two command buttons for "Go" and "Close".

    I want to know if it's possible in VBA for the user to select a date and customer in the userform, click 'Go', find the rows which contains the date in column 'I' and the customer name in column 'F' but only copy the row if column 'E' has a Y or y and column 'H' has a PASS.

    The rows will then be copied into testmailout and testregister will close (this step is important)

    Any help would be apprechiated and if possible can you explain what parts of the code is doing please.

    This will be used by staff who are computer illiterate so it needs to be as automated as possible please.

    If you know anymore info I'll try my best to explain

    Cheers,
    guyinahatgames
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Hi guyinahatgames

    Welcome to the Forum!!!

    The Code in the attached appears to do as you require. I need you to test it. The Code assumes both Workbooks are in the SAME Folder. This can be modified...for testing purposes place them in the SAME Folder.

    This has not yet been done but will be...let's get it working for you.
    can you explain what parts of the code is doing
    Edit: Sorry...basic flaw in the Code...get back to you...
    Last edited by jaslake; 11-07-2014 at 09:24 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Hi guyinahatgames

    The Code in the attached appears to do as you require. I need you to test it. The Code assumes both Workbooks are in the SAME Folder. This can be modified...for testing purposes place them in the SAME Folder.

    Open the testregister, leave testmailout closed, the Code will open it.

    This has not yet been done but will be...let's get it working for you.
    can you explain what parts of the code is doing
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-06-2014
    Location
    UK
    MS-Off Ver
    Office 2003
    Posts
    10

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Hi Jaslake

    Wow that's amazing I can't thank you enough that's exactly what I wanted thank you very much :D

    Just a quick question is there a way when copying over into a new sheet it'll only copy unique rows instead of all the duplicates?

    In the mean time I'm going to try my best to learn what exactly is going on behind the scenes I only recognise a couble of the kinds and any time you can give to update what's happening is much apprechaited.

    I only wish I was actually good at Excel forumas to actually contribute to the forums :P

    Cheers,
    guyinahatgames

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Hi guyinahatgames

    The Code in the attached has been modified for this
    only copy unique rows instead of all the duplicates
    I've also annotated the Code...any questions, let me know.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-06-2014
    Location
    UK
    MS-Off Ver
    Office 2003
    Posts
    10

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Hi Jaslake

    Thanks for your help again.

    I've tried running the code but I get error 438 what does this mean please?

    Cheers,
    guyinahatgames

    Highlights the below

    Please Login or Register  to view this content.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Hi guyinahatgames

    I'm sorry, I failed to notice your Excel Version. That line of Code will run only in Excel 2007 and later. I'll need to replace it with a routine appropriate to Excel 2003.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Hi guyinahatgames

    I've revised the Code in the attached for Excel 2003 compatibility. Let me know of issues.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-06-2014
    Location
    UK
    MS-Off Ver
    Office 2003
    Posts
    10

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    Sorry for the late reply I didn't notice there was a new post.

    Wow that's amazing that's everything I needed :D thank you very much for taking the time to show me how it's all done and I've learnt more about what's going on thanks to your notes really apprechiate it.

    Thanks again,
    guyinahatgames

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform to find specfic data with certain criterias and then copy to another workbook

    You're welcome...glad I could help.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ 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. Copy data from a Userform to multiple worksheets in same workbook
    By kx1bn7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2014, 09:31 AM
  2. [SOLVED] Macro to find data in source workbook and copy paste to target workbook
    By D.Lovell in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-23-2014, 06:21 AM
  3. Import data from specfic column in Sheet Sheet to List Box userform
    By z-eighty2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2013, 05:54 PM
  4. Copy pivot table data to specfic cells in different workbook
    By Smudge.Smith in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-22-2013, 06:47 AM
  5. if find blank in specfic ranges then blank the specfic range cells
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-09-2012, 11:19 AM

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