Advice wanted fromMVP's.

[Jako]

I have an application which i want to save just a worksheet and not th
entire workbook.

Can someone please suggest the best way to do this.

I would also like it saved with protection so the contents cannot b
changed but can be copied from.

Any advice appreciated

 

[Norman Harker]

Hi Jako!

MVPs are not the only people who can reply to your questions. I could name
quite a few non-MVP contributors who have forgotten more stuff on Excel than
I'll ever know. Indeed in expressing your request this way, you are likely
to cut down your responses all round.

But we're tolerant of newcomers!

I'd use Ron de Bruin's SendMail for this task as it allows you to send a
copy of a single protected sheet. If you send it to yourself, you'll find
that you achieved what you want.

See:
Ron de Bruin
http://www.rondebruin.nl/sendmail-addin.htm

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harald Staff]

Adding to Norman:

You may want this into your application, independent of wether Ron's great
app is installed or not.

A single sheet is not a unit when it comes to files. So you must copy or
move it into a new file and then protect. Record a macro while doing this
manually and you're 90% done (save code not included here):

Sub Macro1()
Sheets("Sheet3").Select
Sheets("Sheet3").Copy
ActiveSheet.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End Sub

HTH. Best wishes Harald

 

[Tom Ogilvy]

Activesheet.Copy
' creates a new workbook with just this activesheet
' this new workbook/worksheet are now active
ActiveSheet.Protect Password:="ABCD"
ActiveWorkbook.SaveAs "C:\My Files\MyWorkbook.xls"
ActiveWorkbook.Close Savechanges:=False
' now the original workbook and worksheet are active
ActiveWorkbook.Close SaveChanges:=False

 

[Jako]

Sorry if i caused offense Norman !!!!

A point back at you is i am not a newcomer to this forum.

It's just that the best advice i have always got are from Tom Ogilv
and other MVP's.

I did not intend to alienate anyone or to offend anyone.

As you say many people (non MVP's) also know a great deal (and i
accept, for that matter, far more than me).

Anyone that i offended please accept my sincere apologies.

:eek

 

[Jamie Collins]

...

MVPs are not the only people who can reply to your questions. I could name
quite a few non-MVP contributors who have forgotten more stuff on Excel than
I'll ever know.

A phase you are unlikely to hear from an Excel MVP: 'I've consulted
the other 71 current Excel MVPs and, as well as never having heard
this question before, we've no idea what the solution is.'

Jamie.

--