Protect Worksheet & Save Workbook

Oct 30, 2006

I want to find a code to lock all the cells with data once a button is pressed and with the same button click i want the workbook to be saved.

View 5 Replies


Save Worksheet As New Workbook

May 8, 2014

I have a sheet full of data.All this long I had written the code to save the selected cells as a PDF.But now I want it to be saved as a separate file. In this case, I believe, first the VBA will have to open a new workbook. Then copy paste this sheet onto one of the sheets in the workbook . then save the .XLM file.

[Code] ..

View 7 Replies View Related

Save One Worksheet From Workbook

Apr 19, 2007

I have a save button on a user form that saves a workbook

Private Sub SaveBookbtn_Click()
' SaveAs using data from workbook cells
Dim ThisFile As String
Dim TheFilename As String

ThisFile = Sheets("Sheet1").Range("A2").Value
TheFilename = Application.GetSaveAsFilename _
(ThisFile, filefilter:="Excel Files, *.xls")
If TheFilename = "False" Then Exit Sub
ThisWorkbook.SaveAs TheFilename
End Sub

But what I would like to do is save just "sheet1", and not the whole workbook, using ThisFile as the name to save it under - where I can choose which directory it can go in. I have tried to modify the code to get the desired results but I can't seem to crack it. Can the above code be modified to do the job or am I going to have to start from scratch?

View 2 Replies View Related

VBA To Copy Worksheet To New Workbook And Save

Oct 19, 2011

How to copy the entire worksheet into a new workbook and save the file to a specific directory with the following filename format ("exceptions191011 - ie. the word exceptions followed by today's date in ddmmyy format).

Here is what I've got so far:


Sub SaveAs()

Dim FName As String
Dim FPath As String

FPath = "G:Exceptions"
FName = "Exceptions" & Format(Date, "ddmmyy") & ".xls"

ThisWorkbook.Sheets("Sheet1").SaveAs Filename:=FPath & "" & FName

End Sub

How to add the following condition that if a file already exists with that name then to bring up an error message stating the file already exists? As well as save the new workbook, it appears to close the original workbook - can I stop this?

View 3 Replies View Related

Save Worksheet In New Excel Workbook?

Nov 22, 2011

I have excel contains multiple sheet. I want to save all sheets as in different workbook in specific folder.

e.g. Workbook "Report" has 3 worksheet i.e. A, B & C By running macro all 3 sheets should be saved as different workbook on "C:Report(folder)"


View 1 Replies View Related

Copy Worksheet To New Workbook & Save

Oct 18, 2006

I have a workbook with a worksheet named "Report"... this sheet and it's macros generate a report from data on another sheet. I'm adding a button to copy the (filled) Report sheet to a new workbook, rename the new sheet in the new workbook "Rep[Name]", then save and close the new workbook. I've recorded a maco of me doing these tasks manually, but what's recorded seems very generic, and does not seem to specify what's really occurring...

For example (in part);
> Rightclick on the Report tab
> Move or Copy
> To Book: (new book)
> [x]Create a Copy
> [OK]


Sub Macro1()
End Sub

This recorded macro does not specify (new book), or Before:"Sheet1" as was the result of my actions... and when replayed back, only makes a copy in the active workbook, before the active sheet...

View 4 Replies View Related

Save Each Worksheet In Workbook As Text

Nov 21, 2007

I have a file consisting of many sheets. I need a macro that saves the file as a normal excel file, that is just a normal save as function. This I have. But then I also want the macro to save one of the sheets as a .txt file. This is what I got from reading this outstanding forum. But this saves all my sheets as seperate .txt files

Sub wsToText()
Dim ws As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
Sheets(ws. Name).Select
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and Settings1kitvelDesktop" & ws.Name & ".txt", _
FileFormat:=xlText, CreateBackup:=False
End Sub

View 2 Replies View Related

Excel 2007 :: Protecting Worksheet / Workbook On Save?

Nov 24, 2011

Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?

View 9 Replies View Related

Macro For Creating Workbook From Every Worksheet And Save By Value Defined By Cell

Mar 26, 2013

have seen macros that create a seperate sheet & save by tab number etc, & wonder if its possible to save each sheet in a workbook as the value defined in each sheets cell A2. is there a vb macro available to do that ? if so where ?

View 9 Replies View Related

Password Protect: Unlock The Worksheet, Autofilter It, Print It, Unfilter It, And Password Protect The Sheet Again?

Jan 12, 2007

I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "rainforest"
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub

View 3 Replies View Related

Protect All Sheets Then Save

Mar 15, 2008

I have a workbook with several Sheets. I wish to automatically protect all sheets with specific user permissions, i.e. Select Unlocked Cells, Insert Rows, Use Auto Filter and Edit Objects, as I would have available when using the manual approach. Also, I wish to invoke a Password in the process.

Additionally, I need this code to operate when the file is Saved or Closed... transparent to the user.

View 9 Replies View Related

Force To Save / Protect Macro

Dec 17, 2009

I've created a file where our accountmanagers can mace price calculations. But eversince i've added a macro to force them to fill the form completely, they have gone and tried to hack the macro (and with succes).

I now would like to add a protection for the macro, so they can only access VBA when "sheet1" is unprotected. How can i do this?

I also would like to create a macro so they only can save the file as a Macro Enabled Excel file (.xlsm),

how can i do this best? .....

View 9 Replies View Related

Locking Workbook :: Tools >protection > Protect Workbook

Jan 21, 2008

I have a workbook with 8 sheets. I have hidden two of the sheets and want to lock the rest from people editing, and stop them from unhiding the hidden sheets

I go to tools>protection > protect workbook

After typing in the password this doesnt protect the workbook as i am still able to edit the cells.

how to lock the all sheets and stop them from being edited?

View 9 Replies View Related

Excel 2003 :: Unable To Allow Users To Make Changes To Workbook But Only Be Able To Do Save As Not Save

Dec 3, 2012

I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.

View 4 Replies View Related

Excel 2010 :: Save Open Worksheet To File And Include Date Of Save?

Mar 11, 2014

am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....

I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....

I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...

View 6 Replies View Related

Save Workbook Using Variable For Save As And For A New Folder Name

Jul 31, 2009

I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.

View 6 Replies View Related

Protect Workbook

Jan 19, 2009

Is there a button to protect a workbook in the same way that you can protect sheets? For example, I don't want a user to be able to touch any locked cells and there's multiple worksheets in this workbook. Do I have to go to each worksheet and protect them or is there a button to protect all worksheets at once?

View 3 Replies View Related

Protect The VBA Of Workbook

Mar 27, 2009

I have created an Excel workbook and protected the sheet. However, it still allows me go into the VBA section (Alt+F11) and edit away to their heart's content.

How can I protect my spreadsheet, as well as the VBA associated with it?

View 4 Replies View Related

Protect The Workbook

Feb 15, 2010

Excel 2007. I have the workbook structure protected with a password. When I open the file, the worksheets are sized to file the normal Excel window space. When I email the spreadsheet to a customer who also has Excel 2007, the worksheets are small and she has to use the arrows to navigate.

We tried changing her view and that didn't work. She does not have the arrows available to resize the window. The only way I could fix it for her was to remove the workbook structure protection. I'm nervous about leaving it this way for our entire customer base as they could unhide hidden sheets and even delete them. Has anyone else experienced this? One more thing, customers that have an earlier version of Excel have not had this problem. This workbook is save as an .xls to be backward compatible.

View 9 Replies View Related

Protect A Worksheet From Changes

Apr 24, 2009

how to protect a worksheet from changes so that I am still able to change one cell on the worksheet but not any other cells.

View 2 Replies View Related

Protect Workbook From Ex-employees

Sep 19, 2008

How can I protect by workbook from ex-employees and the competition from using (I am aware of the weak security measures Excel offers, but something is better than nothing)? Each worksheet is password protected to slow down novice Excel users. However, ex-employees know the password. I was thinking if there was a way to have a changing password (which only a couple people would know) or have the worksheet look for a hidden file on our network, that one of these methods (or another) might work.

View 6 Replies View Related

Protect The Formatting Of Workbook?

Jun 28, 2013

I'm trying to protect my workbook from having people around the office from changing the formatting -- I need standard fonts, outline on the cells, etc. Is there a way for me to protect the formatting, but still keep the cells open and editable for new and updated information?

View 1 Replies View Related

Protect Workbook No Password

Jan 11, 2010

I have a workbook that contains a series of macros designed to compare various reports and spit out a report. I don't want my users to have to insert a password, but I want to keep my workbook protected. I've already protected the macros in the VBA Properties, so those are pretty safe, but the worksheets in the workbook are susceptible to changes that will hose my macros.

Setting the workbook to "Read Only" in Properties on our shared drive helps, but any user can change that at will. Within Excel, going to Options, Security, I can set a password so no-one can modify that workbook, but that pops up a password box when they open the workbook. I'd like to NOT have the password box pop up unless I need to make changes. Is there a way to do that? I've looked on the internet but can't find a solution on this one.

View 5 Replies View Related

How Do I Protect An Entire Workbook

Dec 13, 2006

How do I protect an entire workbook, rather than each worksheet, individually?

Also, I have several worksheets in this workbook. When info is entered in b8, I want this info automatically inserted in all other worksheets within that workbook.

View 9 Replies View Related

Protect Worksheet Upon Closing?

Jul 11, 2013

How can I get my workbook to automatically re-protect a single worksheet in a workbook at the save-then-close point? It doesn't need to be password protected.

View 5 Replies View Related

Protect Worksheet Before Emailing

Mar 30, 2009

I found this link in numerous places on this forum to email just a single worksheet from a workbook using one macro.

HTML Code: ....

View 12 Replies View Related

Protect Worksheet With Password

Jan 21, 2010

the user is prompted with a password inputbox to both protect and unprotect my worksheet named: Data. I am using Excel 2003.

View 3 Replies View Related

Protect The Worksheet With Password

Jun 7, 2008

I had the thought that maybe it would be possible to have a worksheet that is not protected when saved but when it is opened there would be a sub-routine under Sub_Workbook Open that would step through each sheet and assign a random password. And when the workbook is saved it is saved without passwords again.

In this way there would be nothing for a password cracker to pick at. The only flaw I see would be that the password to open the VBA module would need to be static and this would open up the programming to change and remove. But wouldn't this be effective against most users who would only know about the utilities to open the passwords and not the programming behind excel.

View 9 Replies View Related

Protect A Hidden Worksheet

Jun 10, 2005

Is it possible to protect a hidden worksheet. I do not want them to be able to unhide the sheet unless they know a password to do so.

View 9 Replies View Related

Protect Worksheet But Allow Some Features

Sep 5, 2007

I am trying to use protection in order to prevent users from only inserting a row or a column, sort, and autofilter. I don't think Userform is a good choice for this situation. I've tried using the following

Sub MyMacro()
ActiveSheet. Unprotect
End Sub

The problem I'm running into is, when I protect the sheet from the main screen (allowing for everything but those listed above), then run MyMacro, the things that were previously allowed under protection are no longer allowed. Indeed, upon re-protection it prevents the user from accessing anything other then the very basics (selecting cells). How can I make it so that the protection works the same as before the macro?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved