Delete Special Cells And Protected Workbook
Mar 28, 2014
I have a code which deletes all contents of cell in range except formulas, from sheet 2 to 13. Code works, but I have included toggle button on each sheet to lock/unlock sheets.
If I lock sheets, code doesn't erase cells as needed.
I have figured out code that works for me, but looks like It's not performing quite correct. Although code executes quite quick...
This is my current code :
[Code] .....
How could I rearrange this code to work in proper order ?
View 4 Replies
ADVERTISEMENT
Apr 12, 2014
I am using code to filter my 4 sheets Greater then 0 (zero)
After apply above filter now i need to copy multiple rows and paste on another specific workbook for paste i m using below code:
for 1st sheet with the name ("V2")
for 2nd sheet with the name("LV")
For 3rd sheet with the name ("F2")
and 4th sheet with the name("L2")
If I play above code one by one all is going very well,,,,,,or if use in this way all is going very well
But here is a big problem..........if any sheet have no value greater then 0(zero)....then code paste all data... e.g shssts("LV") .Range("C5:C54").Copy but C5:C54 have no data greater then 0(zero) and it will paste on another sheet c5:c54 and again new sheets data will paste below the c54 while c5:c54 have no data.
So I want if any sheet have no data with range is greater then 0(Zero) then skip the copy paste code or use like SpecialCells(xlCellTypeVisible) .
View 5 Replies
View Related
May 13, 2009
I have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.
Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.
I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.
Does anyone know what might be causing this, or is the workbook corrupt?
View 9 Replies
View Related
Jun 30, 2006
Sub import()
On Error Resume Next
Dim wb As Workbook
Dim c As Range
Dim rngTo As Range
For Each c In Range("D1:D10").Cells
Set wb = Workbooks.Open(Filename:=c.Value)
With Workbooks("DigitalTicketMaster.xls"). Sheets("All")
Set rngTo = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
End With
wb.Sheets("Combined").Range("A2:C100").Copy rngTo
wb.Close .............................................
we have a change on our sheet, make it easy for our guys. Column A, is the date. Today 6/29.
They enter it once at the top, and it pastes for them automatically (a simple cell = $E$1)
But now, when I use the macro, it pastes over the formula that I don't want nor need, and have to call the second macro for. But then everything in that column shoes E1 on my new sheet, which is not the date... and each sheet changes dates, so I can't just have the date all the same.
What can I do in my original code to still make it loop, pull all my pages that I tell it to, but paste values only?
Would it be possible, in my loop, to highlight my cells, on that sheet... A2:C100, copy them, paste themselves right onto themselves, as values only, then copy over to my new sheet?
View 9 Replies
View Related
Jul 19, 2007
I am looking for a macro that can do one of two things. I am not sure which way I want to go with this yet and how automated it should be. Either scenario will result in a significant time save.
Scenario One
1. Copy & Paste Special Values on the selected/active worksheet
2. Protect Selected Worksheet with password "1234"
3. Delete ALL worksheets except the selected/active worksheet from the file. Names of other sheets can be various and are not static
For example:
A workbook has the following: sheets1, sheet2, sheet3, sheet_unknown_name
If you are working in sheet1 the macro would copy paste special values the entire sheet, protect sheet1 with the password "1234" and delete any and all other sheets.
Scenario Two
1. Copy & Paste Special Values on the selected/active worksheet into a new workbook
2. Protect the new workbook with password "1234"
3. Rename the new workbook with the original workbook name adding either "em_" to the front or "_em" to the end of the filename
4. File, Send To, Mail Recipient as attachment.
View 4 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 Replies
View Related
Oct 21, 2008
I am trying to come up with some VBA to paste special value an entire workbook.
View 9 Replies
View Related
Apr 4, 2007
Is it possible to delete multiple rows when the worksheet is protected using vba? For Example Peps - Select a undetermined amount of rows and then use a macro to unprotect - delete the selected rows - protect.
View 5 Replies
View Related
Aug 15, 2008
Currently I have a macro which successfully updates a value in the report page, creates a new document and then copies and pastes (as values)that sheet into the new workbook and saves it down as a unique file.
I need to have graphs on this new workbook. So gathered creating a template and getting the macro to open this template, copy the ranges and then paste into the template (which will update the charts) and then save down.
The ranges i need to paste in are: B5:D7, and B11:P37 the destination cells are exactly the same.
The current code i have is: ...
View 4 Replies
View Related
Apr 5, 2014
I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
View 2 Replies
View Related
May 1, 2007
I just noticed that if you already have a workbook protected and then run code that will protect it, it will become unprotected! (I have a line of code to protect the workbook in the workbook_open event; incase the last user unprotected the book on the last use). Apparently it doesn't work like protected worksheets, where if the sheet is password protected and you have code that tries to reprotect it with a different password nothing will happen. I can not find a way to check if the workbook is protected (and if so skip, but if not then protect). Worksheets have "activesheet.protectcontents" to tell you True/False.
View 3 Replies
View Related
Jan 21, 2011
I'm looking to enable changing row height in a protected workbook and am scratching my head as to if there is a combination of checkboxes I need to 'allow', or if there is some other approach.
I'm using a vba macro to change the height of merged cells:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
[Code].....
View 2 Replies
View Related
Mar 10, 2014
I have a workbook that is protected by password. I have to unprotect sheets.
How can I unprotect sheets and workbooks while I am updating or changing?
View 13 Replies
View Related
Apr 23, 2008
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:
View 14 Replies
View Related
Nov 7, 2011
Code:
Sub Open_Test_File()
Dim wb As Workbook
Set wb = Application.Workbooks.Open("DesktopTest File.xls")
End Sub
This very simple little code opens a new workbook.
But if I add a password to the file that I'm going to open the password box appears.
What do I need to add to this code to automatically input this password?
I have tried putting this at the end:
Code:
Password:="123"
but this does not work?
View 2 Replies
View Related
Jan 24, 2012
I have a master for each manager that pulls a few values from each of their employees personal sheets.
The personal sheets (about 30) are individually password protected so only the owners can open them.
This is creating a security issues as the links in the master require the passwords for each of the "source" workbooks.
Is there any way to add some code so that the passwords do not need to be entered each time?
View 9 Replies
View Related
Apr 18, 2009
My question is as follows:
I have two workbooks in the same folder on a shared drive. Workbook2 is linked to workbook1. I have protected Workbook1 with a password protected to limit access.
When my collegues who use workbook2 try to open it, it asks for the password for workbook1 before opening. When they ignore that and go a head to open the file. The new information from workbook1 is not updated into workbook2.
How can i mentain my password in workbook1, and keep the files linked such that they can be able to see the new information in workbook2 without knowing the password for workbook1.
View 9 Replies
View Related
Jun 15, 2009
I have a workbook that many moons ago I protected. Now I want the workbook to be an unprotected workbook so many users can view and update if necessary.
Typically I know you are supposed to go to: Review, Changes, Unprotect Workbook. But all that is available is to protect the workbook, but it is already protected. I know this because every time I open the workbook, I am prompted to enter a password.
Is there another way to remove this workbook protection I do not know about?
View 9 Replies
View Related
Aug 25, 2006
I've got two files: the one that is the source file and the second one which is the target file. The target file is password protected (let us take the number 1 as the password in this case). I have a macro that would copy the value of an example cell to the target file:
Sub iso()
Dim wBook As Workbook
Range("A2").Activate
ActiveCell.Copy
On Error Resume Next
Set wBook = Workbooks("Test2.xls")
If wBook Is Nothing Then
ChDir "D:"
Set oWB = Workbooks.Open(Filename:="D: est2.xls")
to add a line (or two) to this code, that would open and unprotect the target file and then at the and protect the target file with the same password.
View 8 Replies
View Related
Apr 21, 2014
I'm closing in on the final stage of a large project and I've run into a road block. The following line of code is preventing my users from being able to select specific page ranges when using the Print Entire Workbook option in the print menu.
[Code] .....
I've attached the latest version of my form (with significant support by jaslake) to explain what I'm attempting here. The attached form has several macros that activate upon open; including a user form. The password for the workbook/worksheets/VBE is "j".
Attached File : HCT-RCS ITP V1.89.xlsm
View 14 Replies
View Related
Jun 5, 2014
I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?
View 1 Replies
View Related
Apr 22, 2009
I would like to know how the following code provided in the Excel Help Best Practice Forums , used to enable outlining on a protected sheet, can be applied to a shared workbook.
Private Sub Workbook_Open()
With Sheet1
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub
After enabling 'Share Workbook' and re-opening the file it presents the following error:
Run-time error '1004': Method 'Protect' of object '_Worksheet' failed
View 2 Replies
View Related
Mar 1, 2012
I need something to recover or break a password from a protected workbook.
View 1 Replies
View Related
Aug 20, 2008
I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?
Question: How can I group/ungroup columns in a shared (and protected) workbook?
View 6 Replies
View Related
Jul 7, 2014
I've 10 workbooks (which represent different areas around the factory) that populate a master workbook (belonging to HR).
We've now started to password protect the 10 workbooks. When i open the master workbook and click update - i get prompted to enter the passwords of the 10 workbooks
My question - is it possbile to automatically have the passwords entered so that the master can get updated?
View 1 Replies
View Related
Nov 21, 2013
I have a workbook with multiple sheets. I would like to protect both the structure and window, but when I do this the sheet tabs are no longer visible when I reopen the workbook.
View 4 Replies
View Related
Jun 6, 2014
In my office, our colleague locked his macros before he left. Now, I would like to continue in that but I have no idea, how to get into macro - to study it and change it as needed now.
How to open it?
locked one.xlsm
View 2 Replies
View Related
Mar 13, 2008
I have a macro with "ActiveWorkbook.Protect Password:="password"" . It was working fine in excel 2003. In excel 2007, because of this I am not able to run any macro's in the workbook. I am getting a message "The macro may not be available in this workbook or all macros may be disabled. I can see a security warning in message bar saying macros have been disabled, but I dont have have a option to enable the macros.
View 9 Replies
View Related
Aug 18, 2006
I was handed a workbook that is password protected. The worksheets are not password protected. Because the workbook is protected, I cannot add or delete worksheets.
I figured that I could get around this by copying the worksheets to another workbook, but I can't even do that. Naturally, no one remembers what the password is.
View 3 Replies
View Related
Nov 24, 2012
Copy over data from different workbooks and using paste special values to paste it into a new workbook using a macro. Here is what I have and what I am looking for:
My file path is
C:Documents and SettingsMy DocumentsProjectCostsDecember12
In this folder I have workbooks called:
Function1
Function2
Function3
In each workbook I have 4 worksheets
Cashable12-13
NonCashable12-13
Total12-13
GrandTotal12-13
I also have a workbook called DecMonthlyTotal in the same folder with the same named worksheets.
I am looking for a macro to be placed in the DecMonthlyTotal that will pull the data from the Cashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name Cashable12-13, it will also pull the data from the NonCashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name NonCashable12-13
Both the Cashable12-13 and the NonCashable12-13 have Columns A - G The row that the macro should start the copying from is Row 3 for each of the workbooks; however I don't have an end row for the workbooks as this will vary.
I am using Excel version 2003.
View 1 Replies
View Related
