View Full Version : Solved: Toggle between 2 workbooks

12-06-2006, 01:18 PM
I've got VBA code that interacts with 2 spreadsheets. The first the user opens (and so the spreadsheet name varies:dunno ) and the second the code specifically opens, compares and extracts info back into the first. What convention is used to toggle between the two workbooks when 1 workbook name is always changing and one is always the same? :banghead:

12-06-2006, 01:42 PM
You shouldn't need to 'toggle' between them.

What you should do is create references to them and use them in code whenever you want to refer to them.

For example,

Set wbThis =ThisWorkbook ' create a reference to the workbook the code is in

Set wbAct = ActiveWorkbook ' create a reference to the active workbook

Set wbOpen =Workbooks.Open("C:\MyWorkbook.xls") ' create a reference to the opened workbook

12-06-2006, 01:52 PM
Thanks for the details Norie! I need to make sure I explained how it is setup. The macro is attached to a command button in excel. Order of events:

1 - user opens rotating file.
2 - user starts macro (command button)
3 - macro opens sub workbook to get info
4 - need to toggle back to the original workbook to place info before closing the sub workbook.

I appreciate all your help !!

12-06-2006, 02:22 PM
If by 'toggle' you mean activate/select the workbooks then there is normally no need to actually do that.

Mind you I'm not actually 100% sure what you are doing, so perhaps that is needed.

12-06-2006, 02:26 PM
Save the following in Personal.xls.
It can be accessed from any open workbook and will copy data from a specified location.

Option Explicit
Sub Rotating()
Dim wb As Workbook, ThsBook As Workbook
Set ThsBook = ActiveWorkbook
Set wb = Workbooks.Open("C:\AAA\Test.xls")
wb.Sheets("Sheet1").Range("A1").Copy ThsBook.Sheets("Sheet1").Range("E5")
End Sub

12-07-2006, 08:25 AM
Thanks everyone. Excellent direction and information!!