Cells = Worksheet name

[Jako]

Can anyone please suggest how i could do the following:

For each worksheet in my workbook if there is a value in column "B
then i want the worksheet name to appear in Column "A". There wil
however be varying numbers of rows so i imagine i need to use an xldow
/ xlup type scenario.

For example this is what i would like:

A B C D E
1 Sheet1 0000
2 Sheet1 0320
3 Sheet1 7263
4 Sheet1 2we8
5 Sheet1 hgdtw
6 Sheet1 94ir
etc.

TI

 

[Tom Ogilvy]

for each sh in ActiveWorkbook.Worksheets
set rng = sh.Range(sh.Cells(1,2),sh.Cells(rows.count,2).End(xlup))
for each cell in rng
if not isempty(cell) then
cell.offset(0,-1).Value = sh.name
end if
Next
Next


if you know there won't be any blank cells between the first and last

for each sh in ActiveWorkbook.Worksheets
set rng = sh.Range(sh.Cells(1,2),sh.Cells(rows.count,2).End(xlup))
rng.offset(0,-1).Value = sh.name
Next

 

[Jako]

Many thanks Tom just what i wanted !