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.

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:

dim ws as worksheet

for each ws in activeworkbook.sheets
    '... your whole bunch of search codes here
next

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

Private Sub SEARCH_Click()
   Dim WIP_Number As String, i As Long, lastrow As Long, Found As Boolean
   Dim wb As Workbook
   Dim ws As Worksheet

   WIP_Number = Trim(WIPTEXT.Text)

   Set wb = Workbooks.Open("C:\WIP\WIP_DATA.xlsx", ReadOnly:=True) ' change "C:\WIP\WIP_DATA.xlsx" to your case

   Found = False
   
   For Each ws In wb.Worksheets
      lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
      For i = 2 To lastrow
         If ws.Cells(i, 2).Value = WIP_Number Then
            WIPTEXT.Text = ws.Cells(i, 2).Value
            CARTEXT.Text = ws.Cells(i, 3).Value
            ADVISORTEXT.Text = ws.Cells(i, 11).Value
            REQUIREDTEXT.Text = ws.Cells(i, 6).Text
            COMPLETETEXT.Text = ws.Cells(i, 10).Text
            STATUSTEXT.Text = ws.Cells(i, 14).Value
            AUTHORITYTEXT.Text = ws.Cells(i, 12).Text
            QCTEXT.Text = ws.Cells(i, 13).Text
            TEAMTEXT.Text = ws.Cells(i, 9).Text
            Found = True
         End If
         If Found = True Then Exit For
      Next i
      If Found = True Then Exit For
   Next ws

End Sub

I asume a WIP number only exists once else we will need to change some things

I dont know a way to open it without showing

but it could be closed strait after getting the data

   wb.Close SaveChanges:=False

   Set wb = Nothing

add the above code in between Next ws and End Sub

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