Exporting data to excel file with code

[NDBC]

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

 

[Jacob Skaria]

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

 

[NDBC]

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]

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]

Correction..in one line

wbNew.SaveAs strFolder & strFile & ".xls"

 

[Don Guillett]

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