Shockley:
When I run this macro, it complain that j variable not
define, which I define as long, then complain sName not
define, can you help me more on this, thanks.


Lillian
-----Original Message-----
Lillian,
Here's two methods of doing what you want.
HTH,
Shockley


Sub Tester1()
For j = 1 To 3
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
For Each cell In .Columns(2).Cells
sTest = cell.Text
If sTest < Empty Then
For i = 1 To Len(sTest)
s = Right(sTest, i)
If Left(s, 1) = "\" Then
s = Right(s, Len(s) - 1)
Exit For
End If
Next i
.Cells(cell.Row, 3) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub

Sub Tester2()
For j = 1 To 3
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
For Each cell In .Columns(2).Cells
sTest = cell.Text
If sTest < Empty Then
OldSlashPos = 0
Do
SlashPos = InStr(1, sTest, "\")
If SlashPos 0 Then
OldSlashPos = OldSlashPos +
SlashPos
sTest = Mid(sTest, SlashPos
+ 1)
Else: Exit Do
End If
Loop
.Cells(cell.Row, 2) = sTest
Else: Exit For
End If
Next cell
End With
Next j
End Sub



"Lillian" wrote in
message
...
I have one excel spreed sheet, they are sheet1, sheet2,
sheet3 within this worksheet is called update.xls, on
each sheet1, sheet2, sheet3 in ColumnB each row has
real
long directory, example:
J:\files\docfiles\amaya01\demand.mcp.wpd, I need just
last file name, which is after last slash "\"
deemand.mcp.wpd, all entire ColumnB has 1000 rows has
different directory, all I need is the file name, do
not
care about the long directory, how to write the macro
just chop out last slash "\" before, and just need the
file name.

So this macro need in the loop for
sheet1,sheet2,sheet3.
thanks for the help

Lillian


.