Repost: Activate Worksheet

[Guest]

I did get a response to my question yesterday from Jim Thomlinson (thanks!),
but it looks like he refers to a specific file name in his code. For
example: Set wkbOpend = Workbooks.Open("C:\Test.xls"). I really need to use
the variable FName to refer to a file since the file name will change all the
time. Is there any way to do this? Thanks as always.

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

 

[Dick Kusleika]

Workbooks(FName.Value).Activate

It would be better if you didn't activate the workbooks though.

Dim rBNames as Range
Dim rBName as Range
Dim wbActive as Workbook
dim wsActive as Worksheet

Set rBNames = ThisWorkbook.Sheets("Files").Range("xFiles")

For Each rBName in rBNames.Cells
Set wbActive = Workbooks.Open(rBName.Value)
If SheetExists("PT - Selected Mfr", wbActive) Then
Set wsActive = wbActive.Sheets("PT - Selected Mfr")
ClearPivotTable wsActive
BuildPivotTable wsActive
End If
Next rBName

Function SheetExists(sName as String, wb as Workbook) as Boolean
'replace all references to ActiveWorbook with wb
End Function

Sub ClearPivotTable(ws as Worksheet)
'replace all references to ActiveSheet with ws
End Sub

Sub BuildPivotTable (ws as Worksheet)
'replace all references to ActiveSheet with ws
End Sub

 

[Tom Ogilvy]

If you are going to open multiple workbooks - what is the significance of
file2. I can certainly show you how to use a public variable to store a
reference to a file, but running another macro on "file2" doesn't make much
sense if "file2" is actually several files. The code you show guarantees
the user will have multiple workbooks open if the range Files contains
multiple workbook names.

The following code gives me a list of filenames I've chosen from the open
file dialog (file 1).

The code you posted may work on a copy of that list, but the posted code
doesn't do that. (not that it makes any difference to your question - but in
case you thought it does, maybe I don't understand the question).


Regards,
Tom Ogilvy