<Insert Real Name Here aka abxy,
If your code is valid then activate your lines in the code below
Code mostly copied and pasted from the following pages:
http://www.mvps.org/dmcritchie/excel/collections.htm
http://www.mvps.org/dmcritchie/excel/sheets.htm
http://www.mvps.org/dmcritchie/excel/proper.htm

The subroutine will create a new worksheet and place
the worksheet name of each sheet in the workbook
and what is in cell A1 and B1 of each of those sheets
on to the new worksheet. The rest is up to you.

Sub abxy()
Dim C As String, i As Long, flag as long
Dim wkSheet As Worksheet, curSheet As Worksheet
'Create New Sheet
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
'Rename current Sheet (the new sheet)
ActiveSheet.Name = "D" & Format(Now(), "yyyy_mmdd_mmss")
'save name of current sheet
Set curSheet = ActiveSheet
i = 1 'skip top row for your headers
Range("A1:D1") = Array("Sheet Name", "A1", "B1", "C1", "textbox1")
flag = 0
For Each wkSheet In Application.Worksheets
if LCASE(wkSheetName) = "top" then flag = 1
if LCASE(wkSheetName) = "bottom" then flag = 0
If wkSheet.Name = curSheet.Name Then GoTo bypass
' if flag = 0 then goto bypass -- activate if you have a top and bottom sheet
i = i + 1
'ooo--- your code here ----ooo
'MsgBox " -- " & wkSheet.Name
Cells(i, 1) = wkSheet.Name
Cells(i, 2) = wkSheet.Cells(1, 1).Text
Cells(i, 3) = wkSheet.Cells(1, 2).Text
With wkSheet.Range("A1:A275")
' On Error Resume Next
'------ this is your code -------
'-- Set C = .Find(What:=Right("0000000" & textbox1.Value, 7), _
'-- lookat:=xlWhole)
'--------------------------------------
'-- Cells(i, 4) = C=
' On Error GoTo 0
End With
bypass:
Next wkSheet
if i=0 then msgbox "Top Sheet is missing, or was last sheet"
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"abxy " wrote in message ...
i'm still not understanding to well...let me explain with a little more
detail about what I'm trying to do...

on sheet("06") I have order numbers in column A, in column B, I have
the names of the people whose order numbers are in column A. Every
worksheet in the workbook is like this(except for sheets that i've
named "Top" and "Bottom". Every inserted worksheet goes in between
those 2 worksheets). as of right now, I have it so that if I type in
someone's order number in textbox1, then the corresponding name shows
up in label1 on the UserForm. However, I don't know how to make my code
look at more than than just "06", I want it to look at all the sheets
in the workbook, or at least the sheets in between "Top" and "Bottom",
as well as 2 other workbooks in the same format.


---
Message posted from http://www.ExcelForum.com/