Switching between open windows

[Fallenwing]

I have an excel program in which two windows are open. The second window is a program always called "master list.xls", yet the initial window starts out being called "PO Template.xls", but can be saved to anything else. How can I reference window 1 if I do not know what it is called? I just the macro to activate the sheet so further code is performed on it.

 

[SkipVought]

Hi,

Most of the programming, ie referencing ranges, setting values, and other object attributes can and should be done WITHOUT activating and selecting. Here's how to do it...

1. Set workbook objects.

   Dim wbMaster as Workbook, wsOther as Workbook
   For Each wb in Workbooks
      Select case wb.Name
         Case "master list.xls"
            Set wbMaster = wb
         Case Else
            Set wbOther = wb
      End Select
   Next

This assumes that you only have 2 workbooks open in this Application.

2. Use the appropriate workbook object when referecing objects in that object

'assign value in A1 in Master Sheet1 to My String
    MyString = wbMaster.Worksheets("Sheet1").Cells(1,1).Value
'write MyString to Sheet2!B2 in Other workbook
    wbOther.Worksheets("Sheet2").Cells(2,2).Value = MyString

If you want to "copy 'n' paste" then...

   wbMaster.Worksheets("Sheet1").Cells(1,1).Copy _
      Destination:=wbOther.Worksheets("Sheet2").Cells(2,2)

Notice, "the hand never leaves the wrist" ie
NO ACTIVATE AND SELECT!.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com