Protect with "SaveCopyAs"
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 |
Protect with "SaveCopyAs"
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 |
Protect with "SaveCopyAs"
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 |
Protect with "SaveCopyAs"
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 |
Protect with "SaveCopyAs"
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 |
Protect with "SaveCopyAs"
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 |
Protect with "SaveCopyAs"
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 |
All times are GMT +1. The time now is 12:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com