Other Open Workbook

[Guest]

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

 

[Harald Staff]

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

 

[Guest]

Harald,

Thank you! That was very interesting!!!

Here and There! Thats good

Thank you for your time and support, truly appreciated.

Tim

 

[Tom Ogilvy]

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

 

[Guest]

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