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

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

Thanks again Jacob. Other people will be using the file and I won't know
their path names. Is there a way to save it in the same directory as the
current workbook is saved in.



"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

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

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

Correction..in one line

wbNew.SaveAs strFolder & strFile & ".xls"

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


"Jacob Skaria" wrote:

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

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