VBA - copy and paste special

[edelcol]

Hi All,

How would you go about creating a VBA macro that would copy from your SELECTED cells (army ants -----) and paste special values into your ACTIVE cell (cell that is current focus).

Something like this - but where Range"A1" is dynamic to pick the SELECTED cell and Range"B1" is also dynamic to pick the ACTIVE cell.

Sub pastespecial()
'
' pastespecial Macro
'
Range("A1").Copy
Range("B1").pastespecial (xlPasteValues)
End Sub

Any and all suggestions appreciated!

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

There are some limitations to copying and pasting multi-area ranges. Will your selections always be contiguous ranges?

If not, perhaps you could it explain the purpose of the macro and provide an example of the desired result of copying and pasting a non-contiguous range to the active cell.

 

[edelcol]

Hi Jerry,

Thanks for the warm welcome.

Yes, ranges will always be contiguous e.g. A1:A5

Here's an example of what I'm looking for:

Say in cell A1 there is a formula =B1+C1
Say B1 = 5 and C1 = 2

I want to copy the value of A1 (i.e. "7") and paste it into cell F1. I dont want to have to copy, then right click, hit paste special, then values. Instead I want to copy the cell and use a macro shortcut to paste values.

Is there a way to do that?

 

[Jerry Sullivan]

I'm not sure I follow the steps you would want the user to take to get that result.

For the example you gave, would the user select A1:A7, then Ctrl Key, then select cell F1? And would the result be that A1:A7 would be copied and pasted as values to F1:F7?

The ActiveCell will always be part of the Selection but it can be removed from the Cells to be copied if the above is what you want.

Once you introduce a step to run the macro (even a hot key or button) that process seems more cumbersome than manually copying and pasting.

 

[Jerry Sullivan]

Re-reading your Original Post, where you used the expression "your SELECTED cells (army ants -----) " I realized you probably just want to copy the contents of the Clipboard to the ActiveCell.

If that's correct, you could try using this code with a shortcut key.

Sub PasteValuesToActiveCell()
'--attempts to paste contents of clipboard to ActiveCell

 Err.Clear
 On Error Resume Next
 ActiveCell.PasteSpecial (xlPasteValues)
 If Err.Number <> 0 Then
   MsgBox "The clipboard could not be pasted to the ActiveCell"
 End If
 On Error GoTo 0
End Sub

 

[edelcol]

Great! That seems to work.