Shockley:

Good morning, I use the second Tester1(), it run
perfect, this is what I want, thank you so much, if I use
the first Tester1(), the original COlumn A data is gone,
it become is empty column, ColumnB is the file name I
need it, original ColumnB's data move to ColumnC, this
look good too, but the original columnA is completely
gone, any idea?

Thank you so much for the help.

You have a great day.

Lillian

-----Original Message-----
Lillian,

Below is the complete annotated version. I've added a
line to insert a
column so that the original full path ends up in column
3 and the file name
goes in column 2, which is how I think you want it. If
you would rather just
overwrite the full path with the filename in column 2, I
put a version that
will do that below the annotated version (it just
involves changing one
column number and removing the insert command).

To round out your understanding of how the code works I
recommend you look
up in the vb editor Help! some of the special commands
and functions used in
the macro (use the animated help utility), which I list
below:
For Each
Len
Right
Left
With

It also helps to use F8 to walk through the macro line
by line, placing your
cursor over variables as you go to see how their values
change.

Regards,
Shockley


Sub Tester1()
'Loop through the sheets:
For j = 1 To 3
'Assign current-sheet name to a variable:
sName = "sheet" & j
With ThisWorkbook.Sheets(sName)
.Columns(1).Insert
'Loop through the collection of cells _
in Column 3. 'cell' becomes a range _
variable:
For Each cell In .Columns(3).Cells
'Assign the full path to a string
variable:
sTest = cell.Text
'Jump out of the For loop _
at the first empty cell and go to _
the next sheet ('next j'):
If sTest < Empty Then
'Create a string 's' of incrementing
_
length, beginning with the _
right-most character of sTest
and _
adding one character with each _
iteration of the loop, _
each time testing _
whether the added character is _
the slash character. When it is _
the slash character, remove it _
from the string, and the
remaining _
string is your result (the
string _
variable 's'):
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
'Put the result in Column 2:
.Cells(cell.Row, 2) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub


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, 2) = s
Else: Exit For
End If
Next cell
End With
Next j
End Sub


.