Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
attempting to format a color pattern in tab 3, based tab 2 and tab | Excel Discussion (Misc queries) | |||
How do I Propogate Cell Selection Pattern? | Excel Worksheet Functions | |||
Selection Window | Excel Discussion (Misc queries) | |||
AutoFill pattern based on cell locations | Excel Discussion (Misc queries) | |||
How do I change color pattern based upon a formula within a cell? | Excel Worksheet Functions |