I want to select the rows that i want to copy then I would like a command button to unlock the sheet using password "2014", copy & paste my selection in the last empty row, and then lock the sheet.
View 9 RepliesI am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
I would like some code to allow the user to select any row in Sheet1
and then the populated cells (10 in every case) in that row will copy across to various locations in Sheet2.
eg,
The user selects Row header for Row 12 which has 10 cells ( A12 to J12) populated.
A12 will go to C3 on Sheet 2, B12 to D5, C12 to D9, etc, etc.
i need to copy the selected cells, and paste them on another sheet on same locations, this should be done on button click
View 6 Replies View Relatedfiguring out a code for a command button.
I have 2 sheets open with the following set up:
Sheet1
A2 = Name (chosen off sheet2)
A3 = Job Title (chosen off sheet2)
A4 = Department (chosen off sheet2)
Sheet2
Column A = List of names
Column B = Corresponding Job Title
Column C = Corresponding Department
I need to be able to choose a name off sheet2, click the command button and it send selected name, job title, and department to sheet1 to the respective cells.
Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names
E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown
The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.
I have been really trying to get this sorted myself and I can get it to work with one exception. I can't get the sheet to lock with a password. It locks, but if I try to unlock it again, it does so without prompting me to enter a password.
Essentially I am wanting to unprotect two sheets, unlock the cells that were previously locked (so users couldn't enter data in them when the sheet is protected), then reapply the same password to protect the sheet, but now they will be able to enter data in the unlocked range of cells).
The second macro is to reverse the changes made in the first and 're-lock' the unlocked cells) (and again apply the same password to the sheet).
this is the code I have used.
Sub PART_YEAR()
Sheets("2012 Calculator 1 week").Select
ActiveSheet.Unprotect "taado"
Range("D20:D23").Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("2012 Calculator 2 week").Select
[Code]...
I am assuming I have superfluous code in there, in addition to my mistake with the reapplication of the password.
Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?
View 9 Replies View Relatedfind attached zip file . if you open the file then you can understand the problem.
I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)
Kindly open attahced file i putted the note inside the file.
i have a workbook that has a lot of sheets but i need to pull information from the one sheet "Veneer Log" i Need it to make new sheets with the same heading as on the "Veneer Log" (Rows 1 & 2) Sheet but it needs to be filtered by the "Product" Column (H) with a new sheet made for all the diffrent products i.e. Dimensional, Drywall, Corners - Thin V., Accents,..... so each product will have a new sheet with i am hoping someone can help me with this. This log changes Daily and it would be nice to have a sheet with only the same product on it to compare new orders so we can batch run. i hope i have given you enough information so someone can help me with this. i have attached a sample log the real log has about 10 worksheet for diffrent departments but i only need info from the Veneer Log Sheet.
View 14 Replies View RelatedI have a sheet with the pricelist and a sheet with the actual price offer. I would like to copy selected Items from the price list to the price offer. I dont want to do that by copy and paste method. I would like to create check boxes, so that I can check / uncheck each Item I want to be copied and then press the button copy to the price offer ... how can I do that. I asssume I would need a macro for that. The columns in the pricelist and the price offer sheet will be identical of course.
View 8 Replies View RelatedI have a button the brings a dialog box with a list box. on selecting a figure in the listbox and clicking 'OK' the figure is transferred to a cell in a worksheet.
The column under this cell has a formula with the result True/False. The autofilter then selects only 'True', and then should copy the active range to a new sheet, preferably named according to the value first selected.
It must be possible for the user to have several of these ranges copied to different sheets for printing purposes, which is not a problem as this already works with the code.
I have a button on the Players worksheet that has this
Why would it not work?
Range("NewData").Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
Sheets("Players").Range("A3").PasteSpecial Paste:=xlValues
I am trying to copy Column B starting at B5 down to the last used cell in column B. into the players sheet starting at cell a3.
I try it and it highlights 4 cells and that is it???
Michael
Hi excel masters, I have questions on how to built a macro to copy selected the data from one sheet (contains data) to a summary sheet, I used excel 2007.
The macro I want to run to achive those result:
The data on the data sheet listed similar like below:
in first column (contains name) as below,
xx
xx
xx
xx
yy
yy
zz
zz
in the summary sheet, I want to copy one particular name from datasheet to cell A17 and below, based on the the selection criteria I enter in cell B1.
For example, if I want to show xx in summary sheet, I input xx in cell B1, and run macro to list xx one by one from A17, A18, A19....
I need to macro to run to search entire list to include the value I selected.
I need to copy only user selected rows ( multiple) from one sheet to another. I see similar threads here but none that work with user selected rows.
View 4 Replies View RelatedI have a sheet that has columns from A to BS, and the column headers start in Row two.
I need to 1st filter the data by Column E where the data in Column E should not contain a particular Value, like"Sleeve".
Then it needs to copy the Columns A,D,G, BN, BO, BP,BP,BR & BS.
And it needs to delete Rows 3 & 4 from the current sheet, before pasting it in a new sheet.
And at the bottom of the sheet it needs to give me a count of the rows and the month end date for each month.
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
I have a table and need to protect the sheet. From searching I see its a common problem as the table wont auto expand on a protected sheet.
I was thinking maybe I could create some sort of change event so if a user selects cells on Column A,B,G,H rows 13-1000 the sheet will auto unlock then lock again once they leave those cells.
I think it may need a few seconds delay to give table chance to autoexpand before sheet is locked again.
I am using vlookup to get some data from one shhet in my workbook onto the front sheet
so I have a search box and the vlookup duly does as it should and gets me the data into E28:E100 and fills the cells to the right with corresponding data for each item
Now I select any cell from the first column of the search results I want and it gets pasted into the next empty cell range C4:C22 all this works a treat thanks to lots of help from mr excel. What I want to do (without any more vlookups) is pick up the corresponding cells to the right of the selected cell and paste them into the cells to the right of the pasted cell. I cant do it all at once because there is a different amount of columns between the select area and the paste area... hope this is clear
here is a snippet of code that lets me choose the cell to paste
Dim UserResponse as range
'InputBox to Ask the user to select the cell they want copying
'First direct them to the start cell in the column they are picking from
Range("E28").Select
On Error Resume Next
Set userResponse = Application.InputBox("Choose from List Below", Default:=Selection.Address, Type:=8)
On Error GoTo 0
If userResponse Is Nothing Then
End
Else
userResponse.Copy
(here I want to get the address of the cell into store so I can pick up the addresss of 2 cells to the right of it ie selected cell is E54 and I want to copy that and G54 and N24 and paste them into C8 D8 and F8 repectively)
End If
This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet:
With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.
I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.
I have set up the attached workbook to try to show the results that I need using the matching shaded cells in the worksheets.
The first part of the problem is that all data in columns A, B, C & H (Input1) needs to be copied to columns A, B, C & F in Input2 with the addition of a Zero value in columns D & E (sample data lines 1 - 3).
The second part of the problem is that all data in columns A, B, & F (Input2) where the cell in column C is equal to ZERO needs to be copied to columns A, B & C in Input 3 (sample data Lines 4 - 11).
It would also be useful to be able to copy data in columns A, B, & F (Input2) where the cell in column C is NOT equal to ZERO to columns A, B & C in Input 3 (sample data Lines 12 - 25). However, there will be often be more than value in column C (Input2) dictated by the date in Column A and number in Column B. The value in Column F (Input2) will always be the same for each of those rows, so it does not need to be duplicated in Column C (Input3).
This would have to be manually triggered by linking it to a button or menu item, not sure what would be the best option. As data is entered at various times of the week, the routine would either have to overwrite existing matching data or carry on from where it last finished!
I have some selected cells in column A, they are not consecutive. I would like to copy these values and then copy the same area but from a different column...
The code I have here is only working when I have a consecutive selection.
Code:
Set rng = Selection
fr = rng.Cells(1, 1).Row
lr = rng(rng.Count).Row
rng.Copy
I'm trying to copy data from one sheet to another sheet automatically based on a number selected in a drop down control.
I have used OFFSET to pull some information successfully from a list. This is easy for me to do with single entries and a list and has worked well. I want to do similar but with a dataset not just single cells.
But, I can't seem to make OFFSET work to show a set of data easily.
Perhaps I will need VB code to do so? Or there is another control I'm not aware of? I've done very little with VB.
I'll attach a sample file to try to show better what I'm trying to do.
write a macro - Condition: When i select "Audit Round" = "Round 1" in B2 and press a button it will automatically copy data from B5:B8 and paste special value in C5:C8. Likewise if I select "Audit Round" = "Round 2" in B2 then it should lookup "Round 2" in "Row 4" and paste special values from B5:B8.
Here an example:
A
B
C
[Code]....
Need aid in copying certain cells with text/labels to clipboard to format a body of email to be sent via Yahoo webmail, so I can paste it where needed.
I did a couple of Excel VBA's back in 2000 or so at last job but haven't seen/used VBA since.
Have a spreadsheet with customer data, (one cust per row) and wish to create something like the following in clipboard (in this example, assuming row 2 is the selected row)...I'll only be sending out one or two of these per day so nothing fancy is needed, just a way to avoid retyping critical data that could get mistyped.
--------------------
A8
Rental details for 2008
Customer Name : A2
Unit nbr : A3
Nbr of weeks : A4
Balance due : A5
--------------------
I'm using Office 2000 still...I've done some searching but results found seem to complicated for my situation or not quite close enough for me to see how to apply it.
I have a folder "D:Documents and SettingsRakesh", which has many .xls files. Each file has a sheet called 'Cover Note'. I want to copy cells B2, C2, D4 and F3 from 'Cover Note' of each file.
These cells should be pasted in the current sheet, one row for each file. First cell of each row should have the source file name.
It would be better if macro can prompt to select the directory where ther source files resides.
I have a macro that loops through several workbooks and copies data to one common sheet. Works great with one flaw that I have not been able to resolve. I am using the End Property example below, this works great as long as there are more than one cell with data. With one cell having data this takes you to the bottom of the sheet.
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Need code to select from a specific cell to the last cell in that column with data where when the selected cell is the only one with data that would be the only cell selected.
After going through some posts in this site, I found that, we can use custom validation for locking a cells when the condition is not met.I have tried using that, But I am not able to get it correctly.I have attached the sheet for your ref.
What I want is, when I enter a month in A2 (Which will always be the 1st of that month), that particular column should get unlocked/should be able to edit that month's column only. I have just provided on table here, but what I want is for entire column so that, I can use the same formula for a larger sheet
I need a macro to do this small task for me. Preferably it should execute when I have selected a cell and press a command button. Here's the way it should work:
Copy the cell I have selected
I am self-taught (arguably by a pretty shoddy teacher), and am determined to figure *some* of this stuff out. I need a code to look at the rows on one worksheet and based on the value of column A for each row, copy cells A through E in that row to cells A through E of a blank row on a corresponding worksheet. I found a code from another post that does just what I need it to with one small exception. The code was posted by DaveGuggs and is as follows:
View 2 Replies View Related