daleyman was correct when he talked about looping through the workbooks. I'm going to put together two different examples. The first one uses a global variable, the second one is my preferred method. The reason I dislike global variables is that they take up unnecessary space and they are notoriously difficult to control since anything can change them. For both examples I am assuming that only 2 workbooks are going to be open. 1 workbook is the workbook with the VBA in it, the other workbook is the target workbook with the data that you're going to copy. Since these are just examples, I'll tell you exactly what to do and hopefully you can adapt this to suit your needs. Here we go.
1. Create 2 new workbooks, by default mine were called "Book1.xls" and "Book2.xls", it doesn't matter what they're caled though.
2. In the second book, place this code in "ThisWorkbook":
<pre>
Private Sub Workbook_Open()
FindWorkbook
End Sub</pre>
3. Insert a standard module. (mine is called module1)
4. Put this code in module1:
<pre>
Public oWorkbook As Workbook 'Global variable - bleh
Public Sub FindWorkbook()
If Workbooks.Count = 2 Then 'If there are only two workbooks open
For Each oWorkbook In Workbooks 'Cycle through the workbooks collection
If Not oWorkbook.Name = ThisWorkbook.Name Then 'If the name of the workbook does not equal thisworkbook, then we have found the other book
Exit For
End If
Next
Else
MsgBox "There is only one work book open.", vbInformation, "Workbook Information"
End If
DisplayWorkbookName 'Call sub routine in the other module
End Sub</pre>
5. Create a second module and put this code in it:
<pre>
Public Sub DisplayWorkbookName()
MsgBox oWorkbook.Name
End Sub</pre>
5. Save the book with the VBA and close it down. Keep the other book open.
6. Open the book we've just closed. The message box will display the name of the other open workbook. (oWorkbook is now your Global variable that you requested)
Now, my next post will be very similar, but instead of declaring a global variable, we will be passing the variable from subroutine to subroutine.