+ Reply to Thread
Results 1 to 12 of 12

VBA code to search another workbook

  1. #1
    Registered User
    Join Date
    12-14-2020
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    14

    VBA code to search another workbook

    Hello,

    I'm working in a vehicle service centre and I am attempting to create a spreadsheet to track the work through the workshop.
    I have created a sheet with a working search function but would like to seperate the two so that the service advisors can only search and not change the data in the sheet.
    Basically, I want the service advisors to be able to open an excel file with only 1 tab - the search tab and the workshop controller to open the other excel file to input the data.

    Currently tab 1 is the search and tabs 2,4-15 are where the data will be held.
    When I type a 'WIP number' into the first textbox on my userform and click search, the other 8 text boxes are populated with the data corresponding to that WIP number.

    the code is as follows (if someone could also tell me how to search 'allsheets' instead of each sheet individually that would be appreciated)

    Private Sub SEARCH_Click()
    Dim WIP_Number As String
    WIP_Number = Trim(WIPTEXT.Text)
    lastrow = Worksheets("JAN").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastrow

    If Worksheets("JAN").Cells(i, 2).Value = WIP_Number Then
    WIPTEXT.Text = Worksheets("JAN").Cells(i, 2).Value
    CARTEXT.Text = Worksheets("JAN").Cells(i, 3).Value
    ADVISORTEXT.Text = Worksheets("JAN").Cells(i, 11).Value
    REQUIREDTEXT.Text = Worksheets("JAN").Cells(i, 6).Text
    COMPLETETEXT.Text = Worksheets("JAN").Cells(i, 10).Text
    STATUSTEXT.Text = Worksheets("JAN").Cells(i, 14).Value
    AUTHORITYTEXT.Text = Worksheets("JAN").Cells(i, 12).Text
    QCTEXT.Text = Worksheets("JAN").Cells(i, 13).Text
    TEAMTEXT.Text = Worksheets("JAN").Cells(i, 9).Text
    End If
    Next
    End sub

    I have copied and pasted the code and changed JAN to FEB, MAR etc.
    Last edited by adam1992; 12-15-2020 at 06:43 AM. Reason: Solved

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA code to search another workbook

    Please edit your post to include code tags.

    1) You can alternatively consider just hiding the 'data' sheets; there is an option to also change the Visible property to Very Hidden, which makes it only un-hideable via VBA code or the VBA editor. This way you can kind of prevent users from editing the data sheets.

    2) To search "all" sheets, you need a loop. Something like:
    Please Login or Register  to view this content.
    多么想要告诉你 我好喜欢你

  3. #3
    Registered User
    Join Date
    12-14-2020
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    14

    Re: VBA code to search another workbook

    Updated with sample sheet
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    456

    Re: VBA code to search another workbook

    Hi,

    Make a backup

    Make a new file and copy/move all the tabs JAN .. DEC to it
    Call it "WIP_DATA.xlsx" (or something)
    Save it

    past the following code in the userform2 codetab

    Please Login or Register  to view this content.
    I asume a WIP number only exists once else we will need to change some things
    Please be as complete as possible in your asking so it may save use all the time to rework the solution because you didn't give all the requirements. If you have a layout in mind please work it out first so we can adapt our solution to it. Thanks.
    If you have been helped, maybe you could click the *

  5. #5
    Registered User
    Join Date
    12-14-2020
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    14

    Re: VBA code to search another workbook

    Yes, The WIP number will only exist once,

    Thank you both for your help, i will try it shortly and update

  6. #6
    Registered User
    Join Date
    12-14-2020
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    14

    Re: VBA code to search another workbook

    That's worked great, thank you!

    Just one more question, is there a way to run that code for it to populate the text boxes without opening the other workbook?

  7. #7
    Valued Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    456

    Re: VBA code to search another workbook

    I dont know a way to open it without showing

    but it could be closed strait after getting the data

    Please Login or Register  to view this content.
    add the above code in between Next ws and End Sub

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: VBA code to search another workbook

    Quote Originally Posted by adam1992 View Post
    Just one more question, is there a way to run that code for it to populate the text boxes without opening the other workbook?
    1) are the workosheet names known?
    2) are the headers are all identical?

    If you upload a workbook, it will help.

  9. #9
    Valued Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    456

    Re: VBA code to search another workbook

    If its a big workbook that would take more and more time

    another way to go is make sheets Very Hidden like millz already mentioned

  10. #10
    Registered User
    Join Date
    12-14-2020
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    14

    Re: VBA code to search another workbook

    Quote Originally Posted by jindon View Post
    1) are the workosheet names known?
    2) are the headers are all identical?

    If you upload a workbook, it will help.

    I uploaded the workbook as a seperate comment below the original post - sorry.

    All the sheet names are known (Jan, Feb, Mar etc.) and all the headers are the same

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: VBA code to search another workbook

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    12-14-2020
    Location
    Bahrain
    MS-Off Ver
    2010
    Posts
    14

    Re: VBA code to search another workbook

    All sorted now, thanks everyone

+ 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 search across all worksheets in a workbook
    By jjward101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-22-2020, 04:01 PM
  2. [SOLVED] Code to search Excel and return results even if part of search text is present
    By Taoyuan00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2016, 09:20 AM
  3. Debug Code to Search Entire Workbook
    By atwy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-27-2014, 05:09 AM
  4. VBA Code - Search text & search number & search qty and result - Urgent Please
    By naresh73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2014, 10:51 AM
  5. Replies: 1
    Last Post: 10-18-2012, 05:52 AM
  6. [SOLVED] Fixing code for search engine for whole workbook
    By brownie4321 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-03-2012, 09:55 PM
  7. [SOLVED] Macro ignores search code when 1 of the 2 search parameters is not found
    By BrodyNoochie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2012, 05:21 PM

Tags for this Thread

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