I have a macro that will open a list of hyperlinked files, now i want
to compile information out of those files into a master file. The file
names all start with the job number (e.g. Job3*.xls), and I'd like to
make a new sheet for each job number. I'm pretty new to this, so I
don't even know if this is possible. Once the sheets are created, I've
got the data pulling macro working, but right now I have to create the
new sheets manually first. Thanks

here's a demo. The 'demo' procedure is the entrypoint. all it does is to
allow the user to point at a file and then it passes that filename tothe grab
routine. All your code would need to do is make the same call...

Option Explicit
' this grabs a workbook and copies the data in sheet1 to
' the active workbook
Sub demo()
Dim fn As String
fn = Application.GetOpenFilename()
If UCase(fn) < "FALSE" Then GrabData (fn)
End Sub
Sub GrabData(fn As String)
Dim filename As String
Dim ws As Worksheet
Dim wb As Workbook
Dim source As Range
Set ws = Worksheets.Add()
filename = GetFileName(fn)
ws.Name = filename
Set wb = Workbooks.Open(fn)
Set source = wb.Worksheets(1).UsedRange

With ws
.Range("A1").Resize(source.Rows.Count, source.Columns.Count).Value =
source.Value
End With
wb.Close False


End Sub
Function GetFileName(fn As String) As String
Dim pos As Long
pos = InStrRev(fn, "\")
If pos 0 Then
GetFileName = Mid(fn, pos + 1)
Else
GetFileName = fn
End If
'strip off the extension
GetFileName = Left(GetFileName, Len(GetFileName) - 4)
End Function


"Craig" wrote:

I have a macro that will open a list of hyperlinked files, now i want
to compile information out of those files into a master file. The file
names all start with the job number (e.g. Job3*.xls), and I'd like to
make a new sheet for each job number. I'm pretty new to this, so I
don't even know if this is possible. Once the sheets are created, I've
got the data pulling macro working, but right now I have to create the
new sheets manually first. Thanks