Hi all,

Can anyone tell me how in vba to save a single worksheet as the
worksheets name in a new folder called statistics on the desktop or in
my documents but if the folder does not exist create it first?

Rather than save all my statistic worksheets in the workbook they were
generated in i would like to save them as a read only worksheet in
another folder, this way people can view or copy from an area where
they can have no direct influence on the workbook that created them.

Any ideas?

Regards,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=542456

ActiveSheet.Copy
On Error Resume Next
MkDir "C:\Documents and Settings\bob\Desktop\statistics"
On Error GoTo 0
ActiveWorkbook.SaveAs "C:\Documents and
Settings\bob\Desktop\statistics\" & ActiveSheet.Name

change the location to suit

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Simon Lloyd"
wrote in message
...

Hi all,

Can anyone tell me how in vba to save a single worksheet as the
worksheets name in a new folder called statistics on the desktop or in
my documents but if the folder does not exist create it first?

Rather than save all my statistic worksheets in the workbook they were
generated in i would like to save them as a read only worksheet in
another folder, this way people can view or copy from an area where
they can have no direct influence on the workbook that created them.

Any ideas?

Regards,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=542456

Thanks for the speedy reply bob, is there any way that the statement
you provided can work on all machines i.e if it was a network machin
just put it on the desktop or if it was a personal computer with
couple of users just put it on that users desktop?

Many thanks,


Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=54245

on Error Resume Next
Mkdir "M:\Statistics"
On Error goto 0
Activesheet.copy
sName = "M:\Statistics\" & _
Activesheet.Name & ".xls"
Activeworkbook.SaveAs sName , xlWorkbook.Normal, _
ReadOnlyRecommended:=True
Activeworkbook.Close SaveChanges:=False

--
Regards,
Tom Ogilvy


"Simon Lloyd" wrote:


Hi all,

Can anyone tell me how in vba to save a single worksheet as the
worksheets name in a new folder called statistics on the desktop or in
my documents but if the folder does not exist create it first?

Rather than save all my statistic worksheets in the workbook they were
generated in i would like to save them as a read only worksheet in
another folder, this way people can view or copy from an area where
they can have no direct influence on the workbook that created them.

Any ideas?

Regards,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=542456

You should create an add-in, and then load that add-in on all machines.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Simon Lloyd"
wrote in message
...

Thanks for the speedy reply bob, is there any way that the statements
you provided can work on all machines i.e if it was a network machine
just put it on the desktop or if it was a personal computer with a
couple of users just put it on that users desktop?

Many thanks,


Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=542456