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