MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Hi

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