Copy paste special values from any location to any location.

[MarieTherese]

Hello lovely people,

I've had a look for an answer on this but can't find any solution flexible enough. I'm looking for a macro to replace the shortcut Ctrl + CSV. I've recorded the below code however it copies and pastes the selection in place, where as I want it to copy the selection and paste special values any where in the workbook.

Any ideas appreciated.

Sub PasteValues()
'
' PasteValues Macro
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Selection.Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[L. Howard]

Hi Marie,

This might work for you.

Put in a standard module of the workbook you want to copy from.

Run code and follow the prompts. (You can use your mouse to select what you want to copy.)
Click OK and click on the upper left cell of where you want the copy to go, then OK.

So you can select what you want to copy and if you want to paste it on sheet 3, click on sheet 3 tab, click on the desired upper left cell and OK to paste.
Or you can stay on the same sheet to paste that you copied from to do your paste.

If you want to paste into another workbook, then uncomment this line 'Application.Goto Workbooks("Book2").Sheets("Sheet1").Range("A1")
Make sure the Book name (shown in red) is correct, that is change it in the code to your book name.

Once you have made your copy it will take you to that book as named, sheet1. Then you can select any sheet in the book to paste in.

Give it a go and post back with any question or comments.

Regards,
Howard

Option Explicit

Sub CopyToSheetorToBook()
Dim ColRngFrm As Range
Dim ColRngTo As Range

Set ColRngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _
  Title:="Enter Copy from Column", Type:=8)
  If ColRngFrm Is Nothing Then Exit Sub

*********
'Application.Goto Workbooks("Book2").Sheets("Sheet1").Range("A1")
'*********
Set ColRngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _
  Title:="Enter Copy to Column", Type:=8)
  If ColRngTo Is Nothing Then Exit Sub

MsgBox ColRngTo.Address
ColRngFrm.Copy ColRngTo
   
End Sub

 

[L. Howard]

Woops, my bad...

Change this line:

ColRngFrm.Copy ColRngTo

To this:

CpyRngFrm.Copy
CpyRngTo.PasteSpecial Paste:=xlPasteValues

Regards,
Howard

 

[MarieTherese]

Hey, I'm getting a 'variable not defined' message when I susbtiute your new code.
On another note if I have to go through populating input boxes it will take me longer than just tapping in ctrl + c + alt esv which i can do in my sleep I use it so often

 

[Jonmo1]

Try

Selection.Copy
Range("Z73").PasteSpecial Paste:=xlPasteValues

 

[L. Howard]

My thought also, but you did ask for a macro, didn't you?

If you are not going to use it, I won't pursue the undefined variable.

Regards,
Howred

 

[MarieTherese]

I did indeed ask for a macro, I have a macro to take any selection & paste special text and am looking for one to do the same only values. For me a macro does not mean you have to use input boxes. But thank you for your help.

 

[MarieTherese]

Hello, this just copies my selection inserts a new sheet and paste values in Row 73 Column Z, it's not a dynamic selection.

 

[Jonmo1]

Well you didn't say anything about it being dynamic, you just said to make it paste anywhere.
Z73 certainly qualifies as a member of anywhere.

If you already have a macro that does the copy paste as text.
Can you post that macro, it can be adjusted to do it as values.

 

[MarieTherese]

The title of the thread says from any location to any location if I wanted it to go to Z73 would I not have said that?
If I could get the text macro I set up to work with values I wouldn't be here asking for help..