Replace
Exercise\jeff" & r & ".xls"
with
Exercise\" & Range("I" & r).Value & ".xls"

or you could add a line earlier in the code to assign another variable

eg f = Range("I" & r).Value

then change the filename line to
Exercise\" & f & ".xls"

Ian

"Jholerjo" wrote in message
...
Ian,

If I want to save the file name not with jeff" & r & "but with the value
of
column I, how to I write the code?
I1:Excel
I2:Gurus
I3:etc

file name will be Excel, Gurus, etc. instead of jeff1, jeff2, jeff3.

Many Thanks
Jeff


"Ian" wrote:

Sorry, I was explaining what you should do, rather than sending you the
code
verbatim.
Try this:

Sub test()
For r = 1 To 10
Range("I" & r).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Jeffry Husman\My Documents\Excel_
Workbook\Macro Exercise\jeff" & r & ".xls"
ActiveWindow.Close
Next
End Sub

FYI. The underscore at the end of a line enables you to keep the lines to
a
manageable length. In the code above, ActiveWorkbook to ".xls" is all
treated as one line of code.

The result of running this code should be 10 separate workbooks, each
with a
value in A1. The first workbook will have the value from cell I1 of your
source workbook and the last from cell I10 of the same book. I assume
this
is what you need.

Hope this helps.

Ian

"Jholerjo" wrote in message
...
I'm using recording macro indeed. I have very minimal VBE experience. I
am
revising the coding to:

Sub test()

For r = 1 To 10
Range("I2").Select
Change Range("I2").Select To Range("I" & r).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Jeffry Husman\My Documents\Excel
Workbook\Macro Exercise\jeff" & r & ".xls"

ActiveWindow.Close

Next
End Sub

When try to run it, it says compile error on line Change Range("I2").
Select
to Range("I" & r).Select.

Please advise