Macro Not Running Correct

[jtwusmc]

I am working with a macro (Excel 2010) to match destination formatting. The data I am working with is coming through in image format and with merged cells when I paste it.

The macro below is supposed to automatically format the data so when I paste using Ctrl + V, it matches destination formatting. Here's the delema, when I copy the date and press Ctrl V, the macro does not work, however when I go in and run the macro manually (go into macros, select "Paste Values" then hit run, it works perfectly. Can someone take a look at this and see where I am going wrong here...

Sub Auto_Open()
Application.OnKey "^v", "PasteValues"
End Sub
Sub Auto_Close()
Application.OnKey "^v"
End Sub

Module 1

Public Sub PasteValues() ' Keyboard Shortcut:Ctrl+v
ActiveSheet.PasteSpecial Format:="Unicode Text"
End Sub

 

[AlphaFrog]

Where do you have the Autpen macro located? It should be in a standard module like your PasteValues macro.

The more current method is to use a...

Private Sub Workbook_Open()
Application.OnKey "^v", "PasteValues"
End Sub

...event macro located in the ThisWorkbook module.

 

[jtwusmc]

Thanks for your help. Quick questions:
1.) Should the "Sub_auto open" be in the same module as the "PasteValues" module or a 2nd module by itself?
2.) Also, should I leave the "Sub Auto Close" where it is at or do the same as the with the "Sub_Auto Open"?

Where do you have the Autpen macro located? It should be in a standard module like your PasteValues macro.

The more current method is to use a...

Private Sub Workbook_Open()
Application.OnKey "^v", "PasteValues"
End Sub

...event macro located in the ThisWorkbook module.

 

[AlphaFrog]

1.) Should the "Sub_auto open" be in the same module as the "PasteValues" module or a 2nd module by itself?
2.) Also, should I leave the "Sub Auto Close" where it is at or do the same as the with the "Sub_Auto Open"?

1.) Your choice. Excel doesn't care. I probably would put them all in one module.
2.) Sub Auto_Close has to be in a standard module as well. I would put it in the same module as you put Autpen, but it doesn't have to be in the same module.

You're welcome. Glad it works for you.

 

[jtwusmc]

Thanks for your help! I got it to work. Also assigned "Ctrl +V" in the Macro options which made it work even better!