Cells = Worksheet name
 

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

--
Message posted from http://www.ExcelForum.com

Cells = Worksheet name
 

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

--
Regards,
Tom Ogilvy

"Jako " wrote in message
...
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 will
however be varying numbers of rows so i imagine i need to use an xldown
/ 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.

TIA


---
Message posted from http://www.ExcelForum.com/

Cells = Worksheet name
 

Many thanks Tom just what i wanted !

--
Message posted from http://www.ExcelForum.com