Window Selection based on name pattern
 

Hi Group,
I was wondering if there is a way in VBA to make that excel
spreadsheet as active window which has name ending with a fixed
string? For example: if a lot of excel spreadsheets are open at the
same time, all in the same session of course, my code should identify
the spreadsheet whose name is ending with something like
xxxxx_practice.xls and make it as active window.

Many thanks!

Best Regards,
Abhijat

Window Selection based on name pattern
 

It might be easier to assign set the sheets to variables then call them by
variable name.

Set ws1 = Workbooks("abc.xls").Sheets("xyz")
Set ws2 = Workbooks("cde.xls").Sheets("mno")

ws2.Activate
ws2.Range("A2").Copy ws2.Range("B5")

You only have to spell out the whole thing once, and use the variable
thereafter.

"Abhijat" wrote:

Hi Group,
I was wondering if there is a way in VBA to make that excel
spreadsheet as active window which has name ending with a fixed
string? For example: if a lot of excel spreadsheets are open at the
same time, all in the same session of course, my code should identify
the spreadsheet whose name is ending with something like
xxxxx_practice.xls and make it as active window.

Many thanks!

Best Regards,
Abhijat

Window Selection based on name pattern
 

Give subroutine a try...

Sub ActivateWS(SheetNameEnding As String)
Dim C As Worksheet
For Each C In Worksheets
If C.Name Like "*" & PartialSheetName Then
C.Activate
Exit For
End If
Next
End Sub

And call it from your own code (macro, function, event procedure, etc.) like
this...

ActivateWS "_practice.xls"

--
Rick (MVP - Excel)


"Abhijat" wrote in message
...
Hi Group,
I was wondering if there is a way in VBA to make that excel
spreadsheet as active window which has name ending with a fixed
string? For example: if a lot of excel spreadsheets are open at the
same time, all in the same session of course, my code should identify
the spreadsheet whose name is ending with something like
xxxxx_practice.xls and make it as active window.

Many thanks!

Best Regards,
Abhijat

Window Selection based on name pattern
 

Thanks Rick and JLGWhiz. I think Rick has better solution to suit my
requirement. I will definitely give it a try.
For the moment, what I am trying to do is

For i = 1 To 15
ActiveWindow.ActivateNext
If Right(ActiveWorkbook.Name, 18) = "_practice.xls" Then
File_Name = ActiveWorkbook.Name
End If
Next i

The assumptions here are a.) not more than 15 files are open in the
excel session and b.) no file other than the file I desire has a
suffix of "_practice.xls". The latter assumption, I guess, is valid
for Rick's code as well. Still I think his methodolgy and approach is
much more sound than mine.
Thanks a lot!

Best Regards,
Abhijat.