If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Paste Value Macro
Here's a macro that works fine for me. It converts a formula in the
cell where the cursor is to a value, or formulas in selected cells to values: Sub ConvertToValue() ' ' Keyboard Shortcut: Ctrl+Shift+D ' Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub What I want is a macro that, after I copy a selection to the clipboard, will allow me to PasteSpecialValuesOnly to the new location. I tried: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False but that didn't work. I added ActiveCell.Select to the beginning, but that didn't work either. What am I missing? -------------------- Once I do that, is there a way to add it to the right-click menu, like one can do so easily in Word? TIA, Andy |
#2
|
|||
|
|||
Paste Value Macro
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_ False, Transpose:=False The SkipBlanks:=_ was the wrong way round on yours --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Paste Value Macro
You don't need to select to copy
range("a1").copy range("b4") or for values only range("b4:b10").value = range("a4:a10").value I did the first macro by recording it, then thought I could edit it do the second one, since recording it didn't work. Here's what I want to do - I want to manually select a range of cells, manually go to a different location, perhaps on a different sheet, and then run a macro to Paste only the Value to the new location (not the format or any formulas). |
#5
|
|||
|
|||
Paste Value Macro
If you record a macro while selecting a cellcopymoving to another
sheetpaste special, you get Sub Macro1() ' Selection.Copy ' Sheets("Sheet6").Select ' Range("G6").Select Selection.PasteSpecial Paste:=xlPasteValues', Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub == I commented out the part about selecting & copying. So, do your selecting & moving and then use this. OR, If not on your toolbar put the copy and paste special values (looks like a box with a 12 on it) and use that. -- Don Guillett SalesAid Software "AA" wrote in message ... You don't need to select to copy range("a1").copy range("b4") or for values only range("b4:b10").value = range("a4:a10").value I did the first macro by recording it, then thought I could edit it do the second one, since recording it didn't work. Here's what I want to do - I want to manually select a range of cells, manually go to a different location, perhaps on a different sheet, and then run a macro to Paste only the Value to the new location (not the format or any formulas). |
#6
|
|||
|
|||
Paste Value Macro
Selection.PasteSpecial Paste:=xlPasteValues', Operation:=xlNone,
SkipBlanks _ :=False, Transpose:=False Don, Arrggh! That worked, and I had gotten there before, and it didn't work. But it did and didn't..... Turns out that it works fine if run from a Shortcut Key or Toolbar Button. But if you try to run it from Alt-F8, or ToolsMacroMacros, it doesn't work because that causes the Excel internal clipboard to empty itself, so there's nothing to paste. So I'm all set (for now). Thanks! Andy |
#7
|
|||
|
|||
Paste Value Macro
You're right. Won't work from altf8. Try altf11 or assign to a shape or add
the paste special icon to your toolbar as I suggested. Right click toolbarcustomizecommandseditlook for the icon that looks like a 12 on a boxdrag to toolbar. I just re-tested. 1. Selected a cell 2. Copied that cell 3. Selected another cell 4. Executed the macro from the vbe. -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... If you record a macro while selecting a cellcopymoving to another sheetpaste special, you get Sub Macro1() ' Selection.Copy ' Sheets("Sheet6").Select ' Range("G6").Select Selection.PasteSpecial Paste:=xlPasteValues', Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub == I commented out the part about selecting & copying. So, do your selecting & moving and then use this. OR, If not on your toolbar put the copy and paste special values (looks like a box with a 12 on it) and use that. -- Don Guillett SalesAid Software "AA" wrote in message ... You don't need to select to copy range("a1").copy range("b4") or for values only range("b4:b10").value = range("a4:a10").value I did the first macro by recording it, then thought I could edit it do the second one, since recording it didn't work. Here's what I want to do - I want to manually select a range of cells, manually go to a different location, perhaps on a different sheet, and then run a macro to Paste only the Value to the new location (not the format or any formulas). |
#8
|
|||
|
|||
Paste Value Macro
assign to a shape or add
the paste special icon to your toolbar as I suggested. I assigned the macro PasteVal() to a hotkey, works fine like that. I also put it on my right click menu, also works fine: Sub CreateRightClick() With Application.CommandBars("Cell").Controls.Add .Caption = "Paste Values" .OnAction = "PasteVal" End With End Sub I was only invoking it from Alt-F8 to troubleshoot it, which was what caused the trouble! |
Thread Tools | |
Display Modes | |
|
|