Try the below macro. Mention the sheetnames...This will create a new workbook
and copy the range to Sheet1; and save ...(specify the path as well)

Sub MyMacro()
Dim strFile As String
Dim wb As Workbook, wbNew As Workbook

Set wb = ActiveWorkbook
Set wbNew = Workbooks.Add
strFile = wb.Sheets("Sheet1").Range("A1")

wb.Sheets("Sheet1").Range("a5:i20").Copy wbNew.Sheets("Sheet1").Range("A5")
wbNew.SaveAs "c:\" & strFile & ".xls"
wbNew.Close
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"NDBC" wrote:

I want to export the results calculated in one worksheet to a new file using
a macro. Lets say the new file name is stored in cell a1 (of the original
worksheet) and the range i want to copy is a5:i20. I want to put it in a5:120
of the new file as well.

Thanks