Try this simple version.Assumes values being copied.
'==========
Option Explicit

Sub copyrangestonewwb()
Dim newfilename As String

newfilename = Range("a1").Value
Sheets.Add after:=Sheets(Sheets.Count)
Sheets("sheet1").Range("a4:a120") _
.Copy Range("a1")
Sheets("sheet2").Range("a4:a120") _
.Copy Range("a1").End(xlDown).Offset(1)
ActiveSheet.Copy
ActiveWorkbook.SaveAs newfilename
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NDBC" wrote in message
...
Another question.

If I wanted to copy the same range from "sheet2" in the current workbook
to
just under the data already in the new workbook on sheet1 how does this
change the code. Can I still refer to it as wbnew or is this going to
create
another wb again.

I should have asked this straight up but I was hoping once I could do one
sheet I would be able to work the rest out myself.

Thanks

"Jacob Skaria" wrote:

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