Paste Special at Selected Cell: VBA

[Anne Troy]

When I paste using this recorded code, I get an error:
Paste Special Method of Range Class Failed.



Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

So, how can I take the contents of the clipboard and paste it at the currently selected cell?

I've looked all over, but everyone's code seems specific to their paste location. I don't want to get involved with the WHERE only the NO FORMAT.

Excel 2007 on Win7 and WinXP

[mdmackillop]

Hi Anne,
I don't get an error with that. Can you post the rest of your code?
Regards
Malcolm

[Anne Troy]

Hello, Malcolm, Darling!

I type 1 2 3 4 into cells A1, A2, A3, and A4.
I select cell B1.
I run this macro and it fails:

Sub PasteSpec()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

[Anne Troy]

PS: Now that I have you, I might not let you go. Do you know how I can run a *.sql file from Excel? I mean...I just want to launch it using "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" :) I would love you forever... (I already do. LOL)

[mdmackillop]

For Q1,
I see what you mean. The clipboard data gets lost. PutInClipboard won't handle ranges in any simple way. Here is a possible workaround



Dim r As Range


Sub MyCopy()
Set r = Selection
End Sub



Sub MyPaste()
Selection.Resize(r.Rows.Count, r.Columns.Count).Value =
r.Value
End Sub



Q2, Not something I've done, but I'll have a look.

Great to see you here again!

[mdmackillop]

Can you use the Shell command?



Shell "C:\Program Files\Microsoft SQL
Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

[Anne Troy]

Trying the paste special, MD. Thanks so much! As for the shell. I don't know the rest of the code. I just want to launch a file on the user's desktop called import.sql with that executable file. Sorry. I should have included the name of the file the first time.

Okay, trying the paste code!

[Anne Troy]

Okay. The paste special works lovely. Now, I just have to figure out how to get them to use it. But I can force... :)
Thanks soooooooooooo much, MD.

[Anne Troy]

So I did another paste special search to see if I can't get it without the user bothering with special buttons and got Oorang's kb:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=957
It looked way to involved when I saw it before, and it was 2003. I took at shot at his sample file and it's beautiful. I'm going to put it in mine and see how it goes.