Macro - Save as

Posted by joseph McGowan on November 27, 2001 2:58 AM

I have a workbook with various sheets, I would like to save the current sheet only as a new file.
Can anyone advise me how to do this manually or by using a macro.

Posted by Robb on November 27, 2001 4:05 AM

Joseph

Try this code:

Sub ShtToNewWkBk()
Dim fileName As String
fileName = "E:\MyNewFile.xls"
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.SaveAs fileName

End Sub

It will copy the active sheet from the workbook in which the code is running and start
a new workbook consisting of only that sheet. The new book will be saved. You will need to amend the
filename etc to suit.

Any help?

Regards

Robb

Posted by joseph on December 03, 2001 3:59 AM

Robb,

Thanks very much, this worked perfectly.
I have been unable to respond for a few days.

Would you be able to tell me what to add to make this new file read only?

Thanks again.
Joe.

Posted by Robb on December 04, 2001 3:22 AM

Joseph

I have added the code you need to set the attributes of the new file to
read only. As you will notice, the file needs to be closed to do this or
it will result in an error. I like to specify file names, so I have amended
the variables to specify path and filename separately.

Sub ShtToNewWkBk()
Dim fileName As String, path As String
fileName = "MyNewFile.xls"
path = "E:\"
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.SaveAs path & fileName
Workbooks(fileName).Close savechanges:=False
SetAttr path & fileName, vbReadOnly
End Sub

Hope this does the trick.

Regards

Robb

, Thanks very much, this worked perfectly.

Posted by joseph mcgowan on December 05, 2001 1:22 PM

Robb,

Thank you so much again.

This will help me greatly.

Hope to learn one day how to do these things myself.
I am sure that you will see my questions many times before that happens. This is a great site.

I have added the code you need to set the attributes of the new file to