Protect with "SaveCopyAs"

[Rick_Stanich]

I am creating a work sheet to be "Saved as" another name, my problem is I wish to protect it at the time of the "save as".

<snipet>
Worksheets("sheet1").Select
Sheets("Sheet1").Copy
ActiveWorkbook.SaveCopyAs vFname 'vFname is new file name as Variant

Any help is appreciated!

[Dave Peterson]

It looks like you're copying a single sheet from a workbook and saving that to a
new workbook.

If that's the case, drop the .savecopyas and use .saveas.

Look at VBA's help for its syntax.

Rick_Stanich wrote:
>
> I am creating a work sheet to be "Saved as" another name, my problem is
> I wish to protect it at the time of the "save as".
>
> <snipet>
> Worksheets("sheet1").Select
> Sheets("Sheet1").Copy
> ActiveWorkbook.SaveCopyAs vFname 'vFname is new file name as Variant
>
> Any help is appreciated!
>
> --
> Rick_Stanich
>
> I am me
> ------------------------------------------------------------------------
> Rick_Stanich's Profile: http://www.excelforum.com/member.php...o&userid=28957
> View this thread: http://www.excelforum.com/showthread...hreadid=527358

--

Dave Peterson

[Rick_Stanich]

That was the course of action I originally took, which caused a seperate problem.
http://www.excelforum.com/showthread.php?t=512358
SaveCopyAs was my work around.

[Dave Peterson]

If the code is in the activeworkbook, then one problem is that if you close the
file with the code, then the macro stops at that point.

So this won't get past the .close line:


ActiveWorkbook.Close SaveChanges:=False 'Close new file
'Open original file
Workbooks.Open Filename:=sFname1

You could use:
dim wkbk as workbook
set wkbk = activeworkbook
Workbooks.Open Filename:=sFname1
wkbk.close savechanges:=false


Rick_Stanich wrote:
>
> That was the course of action I originally took, which caused a seperate
> problem.
> http://www.excelforum.com/showthread.php?t=512358
> SaveCopyAs was my work around.
>
> --
> Rick_Stanich
>
> I am me
> ------------------------------------------------------------------------
> Rick_Stanich's Profile: http://www.excelforum.com/member.php...o&userid=28957
> View this thread: http://www.excelforum.com/showthread...hreadid=527358

--

Dave Peterson

[Rick_Stanich]

Thank you, I will give it a try!

[Dave Peterson]

Ps. Instead of using a variable, it looks like you could just use ThisWorkbook.

Workbooks.Open Filename:=sFname1
Thisworkbook.close savechanges:=false

(But the order is still very important.)

Dave Peterson wrote:
>
> If the code is in the activeworkbook, then one problem is that if you close the
> file with the code, then the macro stops at that point.
>
> So this won't get past the .close line:
>
> ActiveWorkbook.Close SaveChanges:=False 'Close new file
> 'Open original file
> Workbooks.Open Filename:=sFname1
>
> You could use:
> dim wkbk as workbook
> set wkbk = activeworkbook
> Workbooks.Open Filename:=sFname1
> wkbk.close savechanges:=false
>
> Rick_Stanich wrote:
> >
> > That was the course of action I originally took, which caused a seperate
> > problem.
> > http://www.excelforum.com/showthread.php?t=512358
> > SaveCopyAs was my work around.
> >
> > --
> > Rick_Stanich
> >
> > I am me
> > ------------------------------------------------------------------------
> > Rick_Stanich's Profile: http://www.excelforum.com/member.php...o&userid=28957
> > View this thread: http://www.excelforum.com/showthread...hreadid=527358
>
> --
>
> Dave Peterson

--

Dave Peterson

[Rick_Stanich]

File name will be changing constantly, hence the variable.

As for protecting the new work sheets, I resorted to protecting the required sheets prior to "SaveCopyAs" then unprotect the originals after the operation has completed.

Works ok for me.

'new protect scheme
Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection

'Do some code

ActiveWorkbook.Close SaveChanges:=False 'Close new file
Worksheets("sheet1").Select
ActiveSheet.Unprotect ("****")

'End VBA program

Thanks again for your input!