Macro To Paste Values Instead Of Formula When User Uses Ctrl-C And Ctrl-V (no Command Button)
Jun 10, 2013
I was thinking of how the Worksheet_Change(ByVal Target As Range) macro can be used to paste values instead of formula when the user uses Ctrl-C and Ctrl-V. Instead of assigning a macro to a command button for user to activate.
Suppose I have a worksheet where the range ("D7:D56") is where I would want the user to paste his values in it.
I have to factor in the possibility that these values have formulas attached to them, and that the user is not tech-savvy enough to know about the "paste special --> values" functionality of excel, and chooses to use the Ctrl-C, Ctrl-V method instead.
What then appears are ####### which might alarm the user.
Neither would we want the user to press a button (which we can assign the xlpastevalues macro as an alternative) as that adds an extra step for the user. There is also a chance the user might not use the button, or overlooked it.
Is it possible to use the Worksheet_Change(ByVal Target As Range) macro, or sth similar, to paste values instead of formula when user uses Ctrl-C and Ctrl-V?
View 6 Replies
ADVERTISEMENT
Dec 13, 2006
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
View 4 Replies
View Related
Apr 19, 2012
When I copy a cell with CTRL-C and paste elsewhere, a small clipboard pop-up appears to give paste options. The pop-up is the size of an average 'starter' cell. I find it a nuisance as it always covers a cell I might want to paste into, but I cannot see that cell anymore because of the pop-up. How can I get rid of the pop-up?
View 2 Replies
View Related
Jul 30, 2014
I'm currently using Excel 2010. Before, when ctrl+v'ing a cell that wasn't a number (but was, for example, a function), I could immediately after press ctrl, bring up a paste menu, and press v to paste as a value.
So before, if I had a range of cells that were functions (say something simple like "=D3/E3" on F3 and dragged down), I could do the following to paste the range into another worksheet:
1. Ctrl+shift+down to select the entire range
2. Ctrl+C
3. Ctrl+V into a different worksheet (at which point everything shows up as either "#DIV/0!" or as the wrong number, depending on cells to the left)
4. Ctrl (brings up the paste menu)
5. V (selects "values")
6. The entire range is now pasted as values, and not as functions
Recently, however, clicking on "ctrl" after pasting brings up no paste menu. I haven't changed any settings. I will lovingly serenade the first (and second, and third) man (or woman) that figure out what settings I must change or what I must do differently.
View 1 Replies
View Related
Jul 22, 2009
I've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.
The macro I have now is:
Sub Copy()
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub
The column is only 25 rows tall.
View 9 Replies
View Related
Dec 13, 2013
Been using this code...
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/28/2012 by Doug Garn
'
'
Dim Rng As Range
Set Rng = Range(ActiveCell, ActiveCell.Offset(0, 3))
Rng.Select
[Code]...
What it does, I hilite (put focus on 1 cell) a cell and it copies that cell and 3 more to the left, switches to 2nd xls file, pastes that, puts the cell focus on next line, then goes back to the first sheet and I just have it select a random cell(s) so it does a Ctrl-F properly.
Problem is the 3 ways I have above to bring up the find box aren't working for me. It won't find anything and I don't understand why. And I hope I explained this correctly.
View 2 Replies
View Related
Jan 4, 2009
This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet.
Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?
View 4 Replies
View Related
Jun 17, 2012
I have a group of Excel (2003) template files, each with a different name. When these files are opened they ask for certain information and save themselves as a unique name. Within these files are VBA routines and functions and, because these files basically do the same things, they have the same name. These routines exist in their own unique modules each with a different name. Unfortunately, in these files there are differences in the number of columns of information these routines act on so once they have been saved I need them to only act on their own columns only.
My problem is, when I have more than 1 of these files open in the same instance of Excel, I try to run a file using a control letter combination Ctrl-e and it sometimes runs the version of the macro in the wrong file. This leads to all kinds of strange thing happening. What I can do to force the correct macro to work in all instances?
View 2 Replies
View Related
Feb 27, 2013
I'm having a problem creating a Reset All command button that will remove user entered unlocked cell values from an entire workbook save for one specific worksheet. So for example, I have five tabs labeled as "DTF", "Week 1", "Week 2", "Week 3" and "Week 4". I want one single command button (placed in the "DTF" worksheet) to remove all of the unlocked cell values from "Week 1" to "Week 4".
View 9 Replies
View Related
Sep 22, 2006
Is it possible to have a user form command button perform link or steps that are recorded with macro recorder? I am trying to reformat cut and paste a xls raw data file. I am trying to keep my file uploads to a min., but can upload an example if necessary.
View 4 Replies
View Related
Nov 6, 2008
i have tried using the letter M as a shortcut key (because it is to activate a "Move" function) but unfortunately it doesn't work. I gather from this that not all of the keys are available as shortcut keys presumably because they already have function.
Is there a way around this? and/or is there a list somewhere of the available shortcut keys?
View 13 Replies
View Related
Feb 27, 2007
the keyboard shortcut to switch between workbooks in the application seems to have stopped working on my boss' machine.
View 4 Replies
View Related
Aug 15, 2008
This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.
View 3 Replies
View Related
Apr 7, 2013
Can you ctrl-F for only highlighted cells? I want to change all cells with '#VALUE!' in them, however, I only want to do this for a few columns.
View 4 Replies
View Related
Sep 29, 2013
How could I detect if the CTRL (Control) or ALT key has been pressed. I want to execute a macro when either one is press over a given cell.
View 3 Replies
View Related
Mar 26, 2009
I have been searching Excel Help for a code that will allow me to program the Ctrl + Home function.
Specifically, I have a macro that leaves the selected cell too far down on the spreadsheet. Instead of making the user scroll up or press the Ctrl + Home keys, I want to write a code at the end of my existing macro.
View 14 Replies
View Related
Sep 26, 2012
I'm trying to replace Forecast12.xls to SEPFCAST Forecast12.xls but I keep geting error message that says "the name you entered is not valid" and gives me 3 reasons. Both the location of the file is the same and I have spelt it right. Im not sure what is causing this error as it let me change in a different sheet to the file name as above.
My other option is to link manually about 500 cells which i dont think is an option.
View 1 Replies
View Related
May 26, 2014
know the keybaord shortcut to select a range? Indeed, how can i trick excel to use CTRL SHIFT Down in a range with blank data and to select the last data in the range?
View 1 Replies
View Related
Feb 21, 2008
i am trying to modify and existing array formula
=VLOOKUP(R2,TRIM(Codes!$C$3:$D$283),2,FALSE)
but ctrl shift enter does not seem to work. has anybody else encountered this problem?
FYI auto calculate is on, lookup value and table array are all formatted the same. as i said, the formula works but i need it updated for one extra row.
View 9 Replies
View Related
Apr 17, 2008
CTRL+END not move to the last cell on the worksheet. How to make to move to the last cell?
View 9 Replies
View Related
Dec 24, 2008
How to send by holding Ctrl+Alt and i need to press Down Arrow
I tryed with
Sub test()
Application.SendKeys "^+({DOWN})"
End Sub
View 9 Replies
View Related
Mar 4, 2009
I recently upgraded my computer. I noticed I can no longer enter array formulas. When I tried to enter an array formula using CTRL+Shift+Enter, nothing happens. I don't get any error, just nothing happens.
If I do the same exact thing on my old computer, it works - formula is converted and I see the braces {} added as part of my formula. Am I missing a macro or add-in? I'm using Excel 2002 - same version on my old computer. Is there another way to generate an array formula besides using CTRL+Shift+Enter?
View 9 Replies
View Related
Nov 14, 2009
I want to be able to define on excel 2007 what gets cut when I cut and paste
cause I just want text, comments, and font formatting, i dont want the fill of the background or border when I cut and paste
View 9 Replies
View Related
Oct 10, 2006
Is there away to disable the Ctrl PageUp / PageDown function to stop users seeing worksheets in a workbook? I know I can use sheetveryhidden etc, but I am trying to keep the coding as simple as possible. I have found reference to this problem on a couple of occasions within the forums but no definitive answer.
View 4 Replies
View Related
Apr 8, 2009
I have a spreadsheet wherein if I press ctrl-A (normally Select All) it is only selecting a limited range of cells ($B$12:$M$22). If I press ctrl-A again from there then it selects all.
I can see something like that being useful in some cases and annoying in others, but I have no idea what is causing it. Some of the cells in that range are locked and others are not. Some of the cells outside that range are also locked while others are not. In any case, the sheet is not protected.
View 3 Replies
View Related
Apr 10, 2008
I have a ton of cells that contain formulas with arrays. How can I update these without:
clicking the cell
clicking the formula bar
then hitting CTRL+SHIFT+ENTER
View 13 Replies
View Related
Jun 24, 2009
I have a couple of array formulas that were expertly suggested on this very forum. They function spectacularly, but now I need to incorporate them into my code.
The catch is that I am not sure yet if I will a WorksheetFunction solution, or more the likes of the actual "live" formula, e.g., "=SUM(A:Z)"
In either case, I am not sure what the equivalent of CSE is in VBA, or whether the braces/curly brackets can be manually inserted in the live formula.
View 9 Replies
View Related
Apr 8, 2014
Is there any method to disable Ctrl + mouse wheel function in excel 2013, actually I want to disable zoom in and out functionality but don't want to disable normal scrolling.
View 2 Replies
View Related
Jun 12, 2014
I am trying to create a scatterplot from some data I have. I attempt this by selecting multiple columns using Ctrl and clicking on the letters above. This results in it plotting only the first selected column on the Y axis with only 1 2 3... on the X axis.
When I however select multiple columns by clicking on a column and dragging to more columns it does plot as I would expect with the first column as X values and the rest Y values.
I know it is possible to create a graph and add data manually but this is way slower and I think that it should be able to be done differently.
I'm using excel 2010.
View 3 Replies
View Related
Nov 18, 2009
The code itself functions as desired however i am getting the vba message "code execution has been interupted". However if i comment out the line
ActiveSheet. Range("Y6:BC6").Delete Shift:=xlUp. Then all is well - so i'd say that is the culprit.
Sub Test()
If Left(ActiveSheet.Name, 5) = "query" Then
Dim intRow As Integer
intRow = ActiveSheet.Range("Y4").End(xlDown).Offset(1, 0).Row
If intRow > 25 Then
ActiveSheet.Range("Y6:BC6").Delete Shift:=xlUp
intRow = intRow - 1
Else
End If
' Runner 1
ActiveSheet.Range("Y" & intRow).Value = ActiveSheet.Range("A5").Value
ActiveSheet.Range("Z" & intRow).Value = ActiveSheet.Range("M4").Value...................
View 6 Replies
View Related
