Shockley,

Yes, it work, thank you so much. your are the greatest.

Have a happy wonderfuly holiday.

Lillian

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

I see the problem you are having--I hadn't considered
that you might have
data in column A. The

..Columns(1).Insert

statement shifts Column A to the right (actually, it
shifts all columns to
the right, which is why the pathnames end up in Column
C), so Column A
becomes Column B, and Column B is where the macro later
puts the file names,
so, the data that originally was in Column A is being
overwritten. To
remedy this, change the

..Columns(1).Insert

statement to

..Columns(2).Insert

and you will be fine.

You have a great day too!
Shockley



"Lillian" wrote in
message
...
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


.



.