View Full Version : Paste Special at Selected Cell: VBA
Anne Troy
03-02-2012, 02:35 PM
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
03-02-2012, 03:21 PM
Hi Anne,
I don't get an error with that. Can you post the rest of your code?
Regards
Malcolm
Anne Troy
03-02-2012, 03:39 PM
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
03-02-2012, 03:44 PM
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
03-02-2012, 04:16 PM
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
03-02-2012, 04:17 PM
Can you use the Shell command?
Shell "C:\Program Files\Microsoft SQL
Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
Anne Troy
03-02-2012, 05:11 PM
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
03-02-2012, 05:17 PM
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
03-02-2012, 05:23 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.