I posted a thread about 4 days ago with a question similar to this, bu
only got one reply which didn't help much, so here I go again(with mor
detail this time):

this is my code right now:

With Worksheets("06").Range("A1:A275")
Set C = .Find(What:=Right("0000000" & textbox1.Value, 7)
lookat:=xlWhole)


ok, now you see how i'm only referring to one sheet ("06")? Instead
i'd like to refer to all the sheets in the workbook. Either that o
refer to a range of worksheets. I can't use the "sheets(array(sheet1
sheet2)" method because this workbooks is updated daily and I can'
refer to specific sheets unless it's a range to say "everything betwee
this sheet and that sheet" ...actually i need to refer to 3 differen
workbooks (all the same range, and all of which will already be open).

any help? :( :

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

See first code in
Collections
http://www.mvps.org/dmcritchie/excel/collections.htm
and comment out what you don't need

'--dim wkBook as Workbook
dim wkSheet as Worksheet
'--for each wkBook in Workbooks
for each wkSheet in Application.Worksheets
'ooo--- your code here ----ooo
msgbox " -- " & wkSheet.Name
next wkSheet
'--next wkBook


--
---
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 posted a thread about 4 days ago with a question similar to this, but
only got one reply which didn't help much, so here I go again(with more
detail this time):

this is my code right now:

With Worksheets("06").Range("A1:A275")
Set C = .Find(What:=Right("0000000" & textbox1.Value, 7),
lookat:=xlWhole)


ok, now you see how i'm only referring to one sheet ("06")? Instead,
i'd like to refer to all the sheets in the workbook. Either that or
refer to a range of worksheets. I can't use the "sheets(array(sheet1,
sheet2)" method because this workbooks is updated daily and I can't
refer to specific sheets unless it's a range to say "everything between
this sheet and that sheet" ...actually i need to refer to 3 different
workbooks (all the same range, and all of which will already be open).

any help? :( :(


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

i'm still not understanding to well...let me explain with a little mor
detail about what I'm trying to do...

on sheet("06") I have order numbers in column A, in column B, I hav
the names of the people whose order numbers are in column A. Ever
worksheet in the workbook is like this(except for sheets that i'v
named "Top" and "Bottom". Every inserted worksheet goes in betwee
those 2 worksheets). as of right now, I have it so that if I type i
someone's order number in textbox1, then the corresponding name show
up in label1 on the UserForm. However, I don't know how to make my cod
look at more than than just "06", I want it to look at all the sheet
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

<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/

correction on the macro
I left out the period before name in a couple of statements
and I presume "Top" sheet and "Bottom" were not to be
processed so made some changesfor that as well.
If LCase(wkSheet.Name) = "top" Then


Sub abxy()
Dim C As String, i As Long, flag As Long '2004-05-11
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")
Rows("1:1").Font.Bold = True
flag = 0
For Each wkSheet In Application.Worksheets
If LCase(wkSheet.Name) = "top" Then
flag = 1
GoTo bypass
End If
If LCase(wkSheet.Name) = "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