MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

identifying sheet name from the cell

  Asked By: Lynda    Date: Dec 18    Category: MS Office    Views: 1163

I need some help.
I have a number being input in Sheet1, which I have to find in rest
of the worksheets.
I am recording a macro for this.For recording I do Ctrl+F, and type
in the input number in "Find What" textbox. The other settings of
the Find box are:
Within: Workbook
Search : By Rows
Look in : Values
Mach enitre cell contents

Obviously , the first time I hit "Find Next", I stay in the same
sheet and the cell highlighted is the same as the cell in which
input is being done. Therefore, I have to hit "Find Next" again to
reach the first occurance of this number in the rest of the
The macro generates the following snippet:

var_no = Range("A3").Value
Cells.Find(What:=var_no, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
True, SearchFormat:=False).Activate

In this case, the input number was lying in Sheet2

Now, what I want is simply, the name of the sheet (Sheet2 in this
case) where the first occurance of that number is, to be captured in
some variable (say, var_SheetName).



1 Answer Found

Answer #1    Answered By: Johnathan Nelson     Answered On: Dec 18

Try something like


Didn't find what you were looking for? Find more on identifying sheet name from the cell Or get search suggestion and latest updates.