Never mind. Try this...(both answers for both your queries)

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

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

wb.Sheets("Sheet1").Range("a5:i20").Copy wbNew.Sheets("Sheet1").Range("A5")
wb.Sheets("Sheet2").Range("a5:i20").Copy wbNew.Sheets("Sheet1").Range("A21")
wbNew.SaveAs "c:\" & strFile & ".xls"
wbNew.Close
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"NDBC" wrote:

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