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

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

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

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

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

Thank you, I will give it a try!


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

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!


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