My problem seems quite easy to solve but for some reason I cannot get around the error messages. It seems it will not process the paste values argument:
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
VB:
Dim currentWb As Workbook
Dim MasterWb As Workbook
Dim wbName As String
Dim ToolWsName As String
Dim MasterWsName As String
Dim k As Integer
[Code] .....
Following is my query:
There are two workbooks , Workbook1 and Workbook2
Workbook1 has only 1 sheet (Sheet name is Final) with multiple rows Tiger,Lion,Goat etc..
Workbook2 has multiple sheets (Tiger,Lion,Goat .... so on)
Each sheet Tiger, Lion, Goat has different no of rows but same no.of coloums.
I want to copy the data from all the sheets in Workbook2 to one sheet Workbook1
ie., once i execute my macro the final output in Workbook1 should contain Tiger under that all the rows from sheet tiger(Workbook2),Goat under that all the rows from sheet Goat(Workbook2) ,Lion under that all the rows from sheet Lion(Workbook2).
Here rows should be inserted in Workbook1 and in these inserted rows we should copy data from respective sheets of Workbook2
Basically I have a user form that opens up another workbook file. and then hide the user form. Now what I owuld like to do is have a button on the worksheet that will recall the userform and close the workbook.
The user form is frmUsreDataSheet It is in VBAProject(UserformExample.xlsm)
The file or workbook that gets opened is VBAProject(FSO Open Report.xlsx)
I searched the web and there seems to be a lot of conflict about whether it can be done. Some suggestion you create a reference in the FBAProject that houses the userform, and then create a code that references or something like that.
Oh and not sure if I should be using an Activex button or a form control button. but the button will exist on the worksheet.
I have to complete his worksheet but now I am in a memory blank. I have the code set up but I guess I have been staring too much at this code...
I have 2 workbooks... One if for data entry and the other one is for a report.
So workbook1: the user will insert information on sheet1 and click a command button that will transfer the information on the next available row in Workbook2.
Workbook2 is strictly for report purpose. So all information transferred from book1 will be in book2.
I need a way, in book1, to have my userform (already set-up) to search for the matching information in my book2 Column A and update the information from my Book1 sheet2.
I have it set up this way:
VB:
Dim wbArchive As Workbook
Dim wsSrc, wsDst As Worksheet
Dim rngSrc, rngDst As Range
Dim rngReqNo As Range
Dim strReqNo As String
[Code] .....
I need to open a customer-submitted workbook which has a list of items in column B, marked by an 'X' in column A on selected rows. Each customer's list is tailored to them with, say, 25 rows each.
I have a master list with identical formatting but which has the data from ALL lists combined.
I want to insert an 'X' in column A of my master workbook for any matching items for which there is an 'X' in column A of the customer's workbook.
I could do this with a formula in column A of my master list, but each customer's workbook has a unique filename and I don't know how to make the formula use a changing lookup array, so I figured this is something that I have to accomplish with VBA.
The process that will be used is that, I would set up the sheet every month and send it to a data entry person. Then they would send it back to me, and i would send it to certain people for approval. Once they respond with approval I then send it back to the data entry person for filing.
I'm hoping to create a button that will "flip" a switch to to speak.
When I send it to the data entry person the first time, I need certain cells locked and unlocked.
When I send it to the people for approval I need the whole thing locked as well as for when I send it back to the data entry person for filing.
I know how to do this manually, but my issue is that every time I need to send it back to the data entry person at the beginning of the month I have to go over every data entry cell and set it up so that when I lock the sheet they stay unlocked.
So that's where the button would come in...it could even be 2 buttons...Data Entry Locking and then a full Sheet Locking...I would need each password protected too.
I have copied from another post (here I believe) and modified for my situtation. I simply want a range of cells K13:U13 to stay locked unless there is an X in H13. This will repeat on down the spreadsheet, ie. K14:U14 will stay locked unless there is a X in H14. Using the script I found it seems to be working unless you go back and change one of the X's to a blank. The range stays unlocked.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
Dim strRange As String
strName = Me.Range("H13")
If strName = "" Then
Me.Cells.Locked = True
Me.Range("H13").Locked = False
End If
strName = Me.Range("H14")
If strName = "" Then
Me.Cells.Locked = True
Me.Range("H14").Locked = False
End If..................................
I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.
The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.
What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.
I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.
I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....
If anyone has any suggestions I would be eternaly greatful.
Passwords on the sheet for the different buttons are abc or cobra.
You can download my Spreadsheet here.
[url]
I have a workbook with a protected sheet, some cells locked others unlocked, which the users continually update. The problem is some of the unlocked cells are becoming locked, preventing the users from carrying out their role.
I couldn't figure out how they were doing this until I saw Powered Convoy's thread Prevent Unlocked Cells Becoming Locked Via Copy/Paste. This occurs when pasting from another application.
My question is as the title, how best to prevent unlocked cells in a protected sheet from being locked by the user?
I could try to write VBA to intercept all the different ways of pasting data, 5 that I can think of, but was hoping someone else has come across this flaw and has a better or easier solution.
I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.
View 9 Replies View RelatedI have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.
I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.
i have an excel sheet that has a cell with a formula in it. This cell has been locked by the previous user.
I am unable to click on it and it because it is showing a #NAME? error, i need to edit it somehow. Is there some way i can edit it?
I have this code:
Code:
Private Sub WorkSheet_Activate()
'Open workbook and default all main page dynamic data
Application.EnableEvents = False
With Worksheets("MAIN")
.Unprotect
.Range("F3").Value = ""
.Range("O5,T5, F6") = ""
[code]....
It is triggered when protected worksheet "MAIN" is activated. EnableEvents=False prevents any worksheet change triggers as cells are defaulted. Ranges F3. O5, T5, and F6 are cleared. Rows 5 and 6 are hidden (to be revealed again in later code).
Cell F3 is a cell in which user input is required via a validation list. Since the worksheet is protected, it is necessary to unlock range F3 to allow the user to enter data in the cell. EnableEvents is set backto true to allow the worksheet change to trigger when the user enters data in F3.
I am receiving an 'Application-defined or object-defined error' with the line highlighted in red above. (the line that is supposed to unlock cell F3)
I have had a look around and found some answers to this question but not quite as complex and I don't know enough to adapt them correctly.
Basically AX35:AX239 contain a formula which returns "TRUE" or "FALSE" dependant on certain values in the row, what I need is for the corresponding H, I & J cells to be unlocked on each row if the outcome is "TRUE" and no action if "FALSE".
Excel can automatically recognise individuals by their windows login name so you can fully automate the protect/unprotect of the correct sheets without even individuals having to use passwords.
The first step though would be to gather the id's of all users.
1. Create an additional sheet and call it UserLog
2. Put the following code into all the worksheet code modules
[Code] ......
Once you know the windows login names and which sheet they are responsible for you can add the following code to each worksheet.
[Code] .....
I have tried as per below and it is not working.
I have about 50 user that uses one document and will record their comments to instructions, but only 20 users will be able to add the instructions. The document needs to autolock when it is saved and can only be unlock with the username of the 20 users.
I'm using three different workbooks for the macro I've designed. The macro works exactly as I planned it would, but I'm getting a curious side-effect of running the macro. I have over 5,000 workbooks that I need to open, unprotect, change (mostly by copy/paste), and reprotect. It's a fairly straight forward macro, but it is my first, and I must be overlooking something. When the macro is complete, cells that were not modified via the macro in any planned/anticipated way are being unlocked. This has left me puzzled and frustrated. The files are opened, unprotected, the cells are copied over as planned, reprotected, and closed/saved. Everything works perfectly, but the cells in parts of the workbook (Filename) (which were locked before the macro ran) have been unlocked and are no longer protected when the workbook is reprotected. Again, these cells were not within the ranges modified, and I'd rather not have to format every cell in every one of the 5 sheets of (Filename) to be locked.
File names are:
"Finished Goods Inventory TREE (ToDMSI).xls" (alias: Workfile - sheet providing file path to be updated)
"Random_workbook_selected_from_previous_filename.xls" (alias: FileName - the copy-to file being updated)
"MACRO TEST BOM Master.xls" (the copy-from file always open)
Comma Delimited table layout in "Finished Goods Inventory TREE (ToDMSI).xls":
Col A, Col B, Col C, Col D
Customer ID,Item Code,File Name, Directory
4FRE01,4FRE01-0001,4FRE01-0001.xls,4FRONT
Here's the
Public FileName
Public Workfile
Public ItemCode
Public CustCode
Sub OpenBOMSeq()
Testing my worksheet, I found that if I double click on any cell that is not locked, the worksheet becomes unprotected. I need to prevent this from happening. I tried the following code, but it didn't work.
View 6 Replies View RelatedI have a macro in my worksheet that runs as a change event on certain comment cells. This macro unprotects the worksheet, expands the merged comment cell that just change, and re-protects the worksheet. The cell expansion works great. When I re-protect the worksheet all of the cells work correctly, except the cell that was just changed. Any other "unlocked" cell in the document stays unlocked after the protection is applied, but the cell that just changed becomes locked. I need this cell to remain unlocked to allow the user to update the comments. Here is my full
View 3 Replies View RelatedI have a sheet witch has a number of tick boxes and depending on the response a number of hidden rows may open to allow further info to be recorded, how do I protect the sheet in excel 2003 as unlocking certain cells & protecting the sheet will not work.
View 1 Replies View RelatedI need to unlock and lock certain cells when other cells have data entered.
I also need to enforce in the unlocked cells a minimum value.
In my worksheet i have the below requirement
Initially Cells C6 and 7 are unlocked and Cells C5, 8 and 9 are locked
1. Cell C6 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C9 and lock cell C7
2. Cell C7 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C8 and lock cell C6
I also need to unlock cells C15 and 16 when C13 has "Yes" selected in the drop down menu.
Sheet linked from external file, new data coming daily. How to copy Values of cells from B4 till B-empty to C column? The attached file has a properly displayed data.
View 3 Replies View RelatedI want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies View RelatedI have a base document that i can import another data document with a button (this is working).
I then have another WS ("search") in the base document with lots of identifiers which I want to use as my search range to look through the document that I just imported (column A).
I need a msg to ask the user what month they would like to find the $ value on (Ie, January) in the imported WS... this way it doesnt copy the entire line only cell in the selected month column.
Then I want to the user to be able to click a button that will check through the identifiers on the "search" WS and if the same identifier appears in the imported WS in column A, then for the $ value in the column selected to be copied to the search Range work sheet.
If the idenfifier is not matched then in place of the $ value copied can be the string "no in XXX WS" .
I have attached the document with dummy data in each work sheet with details more cleary what I have meantion above.
The following macro does what it is designed to do and needs to be run from a control sheet called "Guide". When I run it from this Guide sheet it stops at around row 53 (out of 1400 rows) on each of the specified sheets in the macro. I have struggled with this problem and have now discovered that the macro will run correctly when run from one of the worksheets specified in the array, e.g. sheet "200 and 100".
Sub Calculateclosingtrades1()
Dim r As Long, c As Integer, LastRow As Long, rcheck As Long
LastRow = Range("J65536").End(xlUp).Row
Dim shtTemp As Worksheet
Dim vntName As Variant
For Each vntName In Array("200 and 100", "100 and 50", "50 and 25", "40 and 20", "20 and 10", "15 and 10", "18 and 9", "200 only", "100 only", "50 only", "40 only", "25 only", "20 only", "15 only").............
I'm trying to run the below code, but every time I do, it locks up excel. The code is running on 900 rows of data within the "loop to delete rows" section and in the main for loop there are 49 values to evaluate for the string strSA. how I can improve this code? When I step through it with F5 it works fine.
View 14 Replies View RelatedPrivate Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
End If
Application.EnableEvents = True
End Sub
everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a
=IF(D2<>"",IF(A2="",NOW(),A2),"")
Whenever I type into field D2 it populates current date and time in field A2 and down the A column as I write into current D columns. The next step I would like to do in which he was able to figure out so far is how to lock in the date and time after something has been typed into D2. If I make any changes to the D2 field it updates the date and time, which I dont want to happen.
I have a user that is trying to lock certian cells within a spreadsheet but to allow certian users still to be able to edit these cells.
When this users is trying to do this and locks the cells it is locking for all users and not applying the "allowed" list of users to make changes.
is it possible to lock a worksheet. For example, i have a single worksheet in a workbook that i do not want people viewing. Is there any way that a password would have to be used to gain access to it? I have to keep the sheet inside this workbook because information is pulling from other sheets inside the workbook.
View 9 Replies View RelatedI did review some examples your members have already provided however I have a slight twist.
I have multiple users adding to a spreadsheet from which data is later copied to a master sheet. I need to lock ONLY cells which contain data when user chooses to save spreadsheet or saves speadsheet on exit. If the cells do not contain data, they should remain unlocked.