If you know the names of the other 3 workbooks and only this workbook will
be variable, but will contain UserGroupReport in the name, then just set a
reference to that workbook using code like the below


Public Sub OpenFiles()

On Error GoTo ErrorHandler

Dim FName As Variant
Dim i As Long
Dim bk as Workbook
FName = Application.GetOpenFilename _
("CSV Files,*.csv", _
MultiSelect:=True)


For i = LBound(FName) To UBound(FName)
Workbooks.Open FName(i)
if Instr(1,activeWorkbook.Name, _
"UserGroupReport",vbTextcompare) then
set bk = ActiveWorkbook
End if

Next

ErrorHandler:

End Sub


After all 4 workbooks are opened, Bk should be a reference to the
UserGroupReport workbook


If you like the Array approach, you can use code similar to the above to
identify which member of the array is the workbook of interest.

--
Regards,
Tom Ogilvy


"hshayh0rn" wrote in message
...
Thanks Bob!! I got it to work. My followup question would be how do I know
that the file will always be the same number within the array? If I code
it
as:

aryWBs(2).Activate - which is what it is right now

How do I know it won't change to 1, 3 or 4?

"Bob Phillips" wrote:

Sorry, that was my fault. The line should read

Set aryWBs(i) = Workbooks.Open(FName(i))


You then activate later without needing to know the name like so

aryWBs(4).Activate

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"hshayh0rn" wrote in message
...
I appreciate your help Bob but I'm by no means a programmer so I'm
getting
a
little lost in the process here. I'm getting a compile error for the
line:

Set aryWBs(i) = Workbooks.Open FName(i)

Also, I don't see how this code will help me activate the worksheet I
mentioned in my initial post. Opening the files is working great and I
can
use 3 of thr 4 right now. I just can't seem to figure our or
understand
from
everyone who has tried to me how to write the code to activate (and
use)
the
fourth workbook/sheet.


"Bob Phillips" wrote:

I would use an array of objects

Public Sub OpenFiles()

On Error GoTo ErrorHandler

Dim FName As Variant
Dim i As Long
Dim aryWBs
FName = Application.GetOpenFilename _
("CSV Files,*.csv", _
MultiSelect:=True)


Redim aryWBs(LBound(FName) To UBound(FName))
For i = LBound(FName) To UBound(FName)
Set aryWBs(i) = Workbooks.Open FName(i)
Application.ScreenUpdating = False

'With Application 'disable the automatic calc feature
'.Calculation = xlManual
'.MaxChange = 0.001
'End With

'Reset Cursor position

Next

ErrorHandler:

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"hshayh0rn" wrote in message
...
Bob,

here is the code I'm using to open the files.

Public Sub OpenFiles()

On Error GoTo ErrorHandler

Dim FName As Variant
Dim i As Long

FName = Application.GetOpenFilename _
("CSV Files,*.csv", _
MultiSelect:=True)


For i = LBound(FName) To UBound(FName)
Workbooks.Open FName(i)
Application.ScreenUpdating = False

'With Application 'disable the automatic calc feature
'.Calculation = xlManual
'.MaxChange = 0.001
'End With

'Reset Cursor position

Next

ErrorHandler:

End Sub

I require the users to open four files. Three of the four have
names
that
are generic and easy to use but it's the fourth one I'm having the
trouble
with. Looking at the code above that I use to open the files how
can I
set
the object variable?

"Bob Phillips" wrote:

When you open the workbook, set an object variable

Set oWB = Workbooks.Open Filename:= ... etc

and then use that object variable when activating.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"hshayh0rn" wrote in
message
...
I have an active worksheet in a workbook that I need to grab
some
data
off
of. The problem is the name of the workbook that the worksheet
resides
in
contains a date and time the workbook was created as part of
the
file
name.
I've tried to use wildcards* to acftivate the workbook but
that
doesn't
seem
to work. Here is an example of the code I'm trying to run that
isn't
working:

Windows(BankNum & "-UserGroupReport" * ".csv").Activate

BankNum comes from user input
-UserGroupReport is static in the name
* represents the part of the file name that contains the
date/time

I don't want to make this really complicated. Does anyone have
any
ideas?