Hi BOB

=RIGHT(activeworkbook.filename,12)

(i'm using 12 as you'll need the *.xls at the end)

so in your code (untested)

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file" & RIGHT(activeworkbook.filename,12)
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

Cheers
JulieD


"BOB" wrote in message
...
Hi gang
I am trying to copy data from one sheet to another. I am copying from
my_file20041001 to my_other_file20041001. I can use the following code to
accomplish this,specific to each sheet, but I would like generic code
that
would extract the last 8 characters of the file name to use for opening
the
other file.

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file20041007.xls"
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

How do I do it?

Thanks