Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to excel file with code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to excel file with code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to excel file with code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to excel file with code
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to excel file with code
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting data to excel file with code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
exporting data to the middle of a text file | Excel Programming | |||
Exporting data to Excel file | Excel Programming | |||
Code to automate exporting to a .txt file | Excel Programming | |||
Exporting to a .txt file - VBA Code | Excel Programming | |||
Exporting data to text file | Excel Programming |