#1. I use this kind of stuff for my shell to open all the excel files in a
single folder:

You can fiddle with it to open your .htm files.

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim DestCell As Range
Dim wkbk As Workbook
Dim wks As Worksheet

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Application.ScreenUpdating = False

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set wkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr), _
UpdateLinks:=0, ReadOnly:=True)
'do a bunch of stuff with that file.
wkbk.Close savechanges:=False
Next fCtr
End If

Application.ScreenUpdating = True

End Sub

===========================

You can always define a variable to hold your info.

Dim A10FromS3 as String

and when you open that file, just extract that value:

a10froms3 = wkbk.worksheets(1).range("a10").value

(but that's just a guess, I don't quite see what you're doing.)

=============
Dim Res as variant
dim rng as range
dim something as string

with wkbk.worksheets("sheet1")
set rng = .range("a:a")
end with

something = "whateveryou're looking up"

res = application.match(something, rng,0)

if iserror(res) then
'no match found
else
msgbox res
end if


" wrote:

OK Dave....
Thought I would give you the update, since your help was
immeasurable...The time report went from 6-7 megs down to around 1meg.
It use to take me 10 minutes or so to complete and now it is down to
about 15 seconds. I works like a CHARM! A few more tweeks and that
project will be finished.
Now, I am on to another challenge....
The 3 things at this point I am working on is:
1) How to have the code to look in a folder and open/activate each
workbook without me explicitly calling each workbook by name. When I
pull reports I place them in a designated folder and name each one
1,2,3 etc...One month I could have 10 in the folder and next month 30.
So, it varies considerably. I currently am using this line:
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\My Documents\ISS RAW
DATA\Current Special Project\s3.htm"
So, you see I would have to add additional statements each time the
workbooks increase...not very efficient.

2) I looked back at the code on LastRow to get some ideas, but hmmm...
What I do is select a cell in the report, say file S3.htm, cell A10 and
use that as a header in another summary workbook, for example, Summary,
cell H1. What I need to have is the code then to open a new file S4.htm
find the cell that I select as the header and place that into Summary
cell I1 and then keep doing that until it cannot find anymore workbooks
in the folder.

3)Last I know that index match does not do very well when you have more
than one potential match...it will only return the 1st one it comes to.
In my situation that may not be the answer that I am looking for...I
have tried index,match match but always get an error message. To
utilize this do you have to setup range names, tables or something
else?

Thanks again,

Hans

--

Dave Peterson