Other Open Workbook
 

Hi...

I have 10 files...1 file will remain open at all times(Master)...Then I open
the other 9 files 1 at a time...

Is there anyway that when the Master file is the one activated, how would I
tell VBA to select the other file without knowing it's name?

Any help is greatly apprecited and thank you for your time.

t

Other Open Workbook
 

Here's one way:

Sub Test()
Dim i As Long
Dim Here As Workbook, There As Workbook
Set Here = ThisWorkbook
Set There = Nothing
If Workbooks.Count < 2 Then Exit Sub
For i = 1 To Workbooks.Count
If Workbooks(i).FullName < Here.FullName Then
Set There = Workbooks(i)
There.Activate
MsgBox There.FullName
Here.Activate
MsgBox "Back"
End If
Next
End Sub

HTH. Best wishes Harald

"MrHappy" skrev i melding
...
Hi...

I have 10 files...1 file will remain open at all times(Master)...Then I
open
the other 9 files 1 at a time...

Is there anyway that when the Master file is the one activated, how would
I
tell VBA to select the other file without knowing it's name?

Any help is greatly apprecited and thank you for your time.

t

Other Open Workbook
 

Harald,

Thank you! That was very interesting!!!

Here and There! Thats good :)

Thank you for your time and support, truly appreciated.

Tim




"Harald Staff" wrote:

Here's one way:

Sub Test()
Dim i As Long
Dim Here As Workbook, There As Workbook
Set Here = ThisWorkbook
Set There = Nothing
If Workbooks.Count < 2 Then Exit Sub
For i = 1 To Workbooks.Count
If Workbooks(i).FullName < Here.FullName Then
Set There = Workbooks(i)
There.Activate
MsgBox There.FullName
Here.Activate
MsgBox "Back"
End If
Next
End Sub

HTH. Best wishes Harald

"MrHappy" skrev i melding
...
Hi...

I have 10 files...1 file will remain open at all times(Master)...Then I
open
the other 9 files 1 at a time...

Is there anyway that when the Master file is the one activated, how would
I
tell VBA to select the other file without knowing it's name?

Any help is greatly apprecited and thank you for your time.

t

Other Open Workbook
 

Probably should adjust this code to ignore workbooks that are not visible,
such as personal.xls.

--
Regards,
Tom Ogilvy

"MrHappy" wrote in message
...
Harald,

Thank you! That was very interesting!!!

Here and There! Thats good :)

Thank you for your time and support, truly appreciated.

Tim




"Harald Staff" wrote:

Here's one way:

Sub Test()
Dim i As Long
Dim Here As Workbook, There As Workbook
Set Here = ThisWorkbook
Set There = Nothing
If Workbooks.Count < 2 Then Exit Sub
For i = 1 To Workbooks.Count
If Workbooks(i).FullName < Here.FullName Then
Set There = Workbooks(i)
There.Activate
MsgBox There.FullName
Here.Activate
MsgBox "Back"
End If
Next
End Sub

HTH. Best wishes Harald

"MrHappy" skrev i melding
...
Hi...

I have 10 files...1 file will remain open at all times(Master)...Then
I
open
the other 9 files 1 at a time...

Is there anyway that when the Master file is the one activated, how
would
I
tell VBA to select the other file without knowing it's name?

Any help is greatly apprecited and thank you for your time.

t

Other Open Workbook
 

Harald,

I have used code similar to this in an application, but one line is causing
a Dr.Watson error when I try and refer to a sheet:

Here.Activate
Sheets("My Sheet").Select <== This line causes the error.
'Range("A2").Select <== With the line above and these
'ActiveSheet.Paste <== lines commented out, the
MsgBox ("Data Pasted") <== MsgBox command works.

etc etc

Am I trying to refer to "My Sheet" in the wrong syntax please?

Thanks,
Ewan



"Harald Staff" wrote:

Here's one way:

Sub Test()
Dim i As Long
Dim Here As Workbook, There As Workbook
Set Here = ThisWorkbook
Set There = Nothing
If Workbooks.Count < 2 Then Exit Sub
For i = 1 To Workbooks.Count
If Workbooks(i).FullName < Here.FullName Then
Set There = Workbooks(i)
There.Activate
MsgBox There.FullName
Here.Activate
MsgBox "Back"
End If
Next
End Sub

HTH. Best wishes Harald

"MrHappy" skrev i melding
...
Hi...

I have 10 files...1 file will remain open at all times(Master)...Then I
open
the other 9 files 1 at a time...

Is there anyway that when the Master file is the one activated, how would
I
tell VBA to select the other file without knowing it's name?

Any help is greatly apprecited and thank you for your time.

t