Thread: How to save a single worksheet as worksheet name in new folder??

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

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" <Simon.Lloyd.27whca_1147782901.6981@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.27whca_1147782901.6981@excelforum-nospam.com...
>
> 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 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

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" <Simon.Lloyd.27wii0_1147784404.973@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.27wii0_1147784404.973@excelforum-nospam.com...
>
> 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
>