Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet reference
I want to do the following:
(say) I have an excel file by name XYZ.xls and this file has many worksheets in it. But I am interested in a specific sheet with name "XYZ all". How to reference the sheet when the filename XYZ could be different every time I run the macro ? Please help. Thanks Srikanth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet reference
Dim bk as Workbook, bk1 as Workbook
Dim sh as Worksheet set bk1 = Nothing for each bk in Application.Workbooks set sh = Nothing On error resume next set sh = bk.worksheets("XYX all") On Error goto 0 if not sh is nothing then set bk1 = bk exit for End if Next if not bk1 is nothing then msgbox "XYX all found in " & bk1.name End if -- Regards, Tom Ogilvy "Srikanth" wrote in message m... I want to do the following: (say) I have an excel file by name XYZ.xls and this file has many worksheets in it. But I am interested in a specific sheet with name "XYZ all". How to reference the sheet when the filename XYZ could be different every time I run the macro ? Please help. Thanks Srikanth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet reference
I read your question that you want to reference a sheet name that consists
of the following: The file name without the ".xls" followed by: A space, followed by: "all" Is that what you want? If so, then something like this might help. Dim ShtName As String ShtName=Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name)-4) ShtName = ShtName & " all" Or maybe I didn't read your question right. HTH Otto "Srikanth" wrote in message m... I want to do the following: (say) I have an excel file by name XYZ.xls and this file has many worksheets in it. But I am interested in a specific sheet with name "XYZ all". How to reference the sheet when the filename XYZ could be different every time I run the macro ? Please help. Thanks Srikanth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
multiple cell reference from sheet to sheet | Excel Worksheet Functions | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) |