Hi all,

I have a workbook with lots of worksheets, but as part of my macro I
want to just save the sheet named data1 as a new file. Is there any
easy way that I can do this?

Thanks in advance,

Joseph Crabtree

Sheets("data1").Copy

Mike F
"joecrabtree" wrote in message
ups.com...
Hi all,

I have a workbook with lots of worksheets, but as part of my macro I
want to just save the sheet named data1 as a new file. Is there any
easy way that I can do this?

Thanks in advance,

Joseph Crabtree

You can use this example in 97-2007
http://www.rondebruin.nl/security.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"joecrabtree" wrote in message ups.com...
Hi all,

I have a workbook with lots of worksheets, but as part of my macro I
want to just save the sheet named data1 as a new file. Is there any
easy way that I can do this?

Thanks in advance,

Joseph Crabtree

Hi

Thats great.

I now want to be able to save all charts in the workbook as a seperate
file. All chart sheets have the name chart1, chart2 etc. Is there
anyway of modifying the codee to do this?

Thanks

Joseph


On Dec 14, 10:15 pm, Rafael Guerreiro Osorio
wrote:
Add the sub CopyToNewWBKandSave and then call it from your code as I did in
sub Test below, supplying it with three arguments, the worksheet object that
refers to data1, a string containing the path to the folder in which you the
new workbook to be saved, and other with the name to be given to the new file

Sub Test()
Dim WKS As Worksheet
Dim SavePath, SaveName As String

'Worksheet object data1
Set WKS = ThisWorkbook.Worksheets("data1")

'Save path & name
SavePath = "C:\"
SaveName = "DataFile.xls"

'Add this line to your macro...
CopyToNewWBKandSave WKS, SavePath, SaveName
End Sub

'...and the sub below as well.
Private Sub CopyToNewWBKandSave(ByRef ToSave As Worksheet, ByVal sPath,
sName As String)
Dim NewWBK As Workbook

ToSave.Copy
Set NewWBK = Workbooks(Workbooks.Count)
NewWBK.SaveAs sPath & sName
End Sub

Best,

R.



"joecrabtree" wrote:
Hi all,

I have a workbook with lots of worksheets, but as part of my macro I
want to just save the sheet named data1 as a new file. Is there any
easy way that I can do this?

Thanks in advance,

Joseph Crabtree- Hide quoted text -- Show quoted text -