cell.parent.name will give you the name of the worksheet of the cell.
The Find method returns a reference to the cell in which it found the
search, so set a range reference to the search results. In order to
search throughout the entire workbook, you may need to write a loop
that goes through each worksheet, performing the search, and breaks
once it finds a match. So, something like:
dim wks as worksheet, cell as range, var_no as variant, strWksName as
string
range("A3").activate ' so that the search will search after this cell
var_no = activecell.value
for each wks in activeworkbook.worksheets
set cell = nothing
set cell = wks.cells.find ...
if not cell is nothing then exit for
next
strWksName = cell.parent.name
On Sep 7, 4:05 am, Abhijat <[EMAIL PROTECTED]> wrote:
> Thanks .. but it doesn't help...
> gives me the initial shhet name.
>
> On Sep 1, 12:37 am, jamesdavey <[EMAIL PROTECTED]> wrote:
>
> > Presumably when it finds the occurrence, it activates the sheet the
> > occurrence is in, so:
>
> > var_SheetName = activesheet.name
>
> > should do it
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
