The following code gives me a list of filenames I've chosen from the open
file dialog (file 1). Other code then opens the first file in the list (call
it file 2), and if a specific sheet name exists in that file, it runs a macro
from the file the list of names is in (file 1). The macro runs in the
correct file (file 2), but I run into problems when I need to run a second
macro from file 1 because I need to activate file 2 again and make sure the
macro runs there. I've been able to do this so far by activating Window 2,
but if the user has multiple files open I'm dead. Since I assign FName to a
variable in the code, can't I refer to FName to reactivate that file? I've
tried a few things but none have worked so far; I'm sure it's something
simple.
Sub ShowFileNames()
Dim FName As Variant
Dim xNames As Range
Sheets("Files").Select
Range("A1").Select
Set xNames = Range("xFiles")
FName = ActiveCell
For Each FName In xNames
Workbooks.Open FName
If SheetExists("PT - Selected Mfr") Then
ClearPivottable
BuildPivottable

Else

End If

When I amn working with multiple open files at one time I like to set
references to those files explicitly something like this...

Sub test()
Dim wbkMain As Workbook
Dim wbkNewBook As Workbook
Dim wbkIsOpen As Workbook
Dim wbkOpened As Workbook

Set wbkMain = ThisWorkbook
Set wbkNewBook = Workbooks.Add
Set wbkIsOpen = Workbooks("ExcessByBranch.xls")
Set wbkOpened = Workbooks.Open("C:\Test.xls")

wbkNewBook.Activate
wbkNewBook.Sheets("Sheet2").Select

End Sub

I can now refer to the workbooks using wbk... which makes moving back and
forth between books a lot easier and to perform and debug as I do not need to
wory about the activeworkbook and such... This reply is a little general but
It should help you get started...

HTH

"cottage6" wrote:

The following code gives me a list of filenames I've chosen from the open
file dialog (file 1). Other code then opens the first file in the list (call
it file 2), and if a specific sheet name exists in that file, it runs a macro
from the file the list of names is in (file 1). The macro runs in the
correct file (file 2), but I run into problems when I need to run a second
macro from file 1 because I need to activate file 2 again and make sure the
macro runs there. I've been able to do this so far by activating Window 2,
but if the user has multiple files open I'm dead. Since I assign FName to a
variable in the code, can't I refer to FName to reactivate that file? I've
tried a few things but none have worked so far; I'm sure it's something
simple.
Sub ShowFileNames()
Dim FName As Variant
Dim xNames As Range
Sheets("Files").Select
Range("A1").Select
Set xNames = Range("xFiles")
FName = ActiveCell
For Each FName In xNames
Workbooks.Open FName
If SheetExists("PT - Selected Mfr") Then
ClearPivottable
BuildPivottable

Else

End If