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]

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

 

[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.)

 

[Rick_Stanich]

Thank you, I will give it a try!

 

[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!