reference worksheet names

Posted by TB on February 11, 2002 1:56 PM

probably an easy one:

i want to write a formula that will print out worksheet names. i want the formula to do a simple test (like...IF the value in B12 is "hello") on ALL worksheets, and print out a list of the worksheets (names) that pass the test. this formula will be on a sort of cover sheet, that will say something like: "The following worksheets pass the test:" and then prints out a list.

how hard would this be to do?

thanks.

-TB-

Posted by DRJ on February 11, 2002 2:01 PM

Lets say you want the list to be in col A on Sheet("Main") and we will check for "Hello" in B12 on each sheet. Modify as needed.

Sub ListEmAll()

For x = 1 to sheets.count

if sheets(x).range("B12").value = "Hello" Then
sheets("Main").range("A65536").end(xlup).offset1,0).value = sheets(x).name
else
end if

next x

end sub

Then you can print this list or whatever.

HTH

DRJ