Help with Paste.

huzman

New Member
Joined
Oct 2, 2003
Messages
29
Hello all! Happy New Year!

I have two macros as follows:

Sub Paste_Excel()

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

End Sub
_______________________________________________________________________
Sub Paste_Text()

ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False

End Sub
_______________________________________________________________________

I want to have one command button which functions as follows: IF the data to be pasted is from Excel, use Paste_Excel ELSEIF the data to be pasted is not from Excel, use Paste_Text.

How do I code this??

Thanks!! :oops:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This seems to work:

Code:
Private Sub CommandButton1_Click()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End Sub
 

huzman

New Member
Joined
Oct 2, 2003
Messages
29
Andrew,

Hello!!

this is the code i have now:

Private Sub CommandButton6_Click()

On Error Resume Next

ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

End Sub

When i try to paste data from within excel, it doesn't work, but when the data is from outside of excel (ex. from Word), it works.

What do you think?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Well in my code the first PasteSpecial method referenced the Selection object not the ActiveSheet object. I expect that's why your version isn't working.
 

huzman

New Member
Joined
Oct 2, 2003
Messages
29

ADVERTISEMENT

I now have the following, the first method still doesn't work. Thanks!!

Private Sub CommandButton6_Click()

On Error Resume Next

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It works for me (XL 2000). Maybe you are selecting a range that is not equal in dimensions to the copied range, thus creating an error. Try selecting a single cell and clicking the button.
 

EDUCATED MONKEY

Board Regular
Joined
Jul 17, 2011
Messages
218
set up office 2007 xp pro ie 8

Hello I have just been looking through the site and found your article, my problem is very similar! I am looking for a solution not really known the cause,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
First here is the code I am using <o:p></o:p>
<o:p></o:p>
ActiveSheet.PasteSpecial Format:="Text", LINK:=False, DisplayAsIcon:= _
False
The situation is I send a url plus a account key to access the site the server then send a reply the format being xml , the code above being used to past the xml in to a worksheet column A:A
Most of the time so about 90% it works fine the other 10% it fails and I cannot see why more over the data collect at the moment of failure is repeated without the failure happening, <o:p></o:p>
Action taken so far I have checked the generation of the URL and found no error, this is confirmed by the fact that the relevant data is returned by the server <o:p></o:p>
I have increased and decreased the Application.Wait time again this has made no difference
I have run the entire batch of URLs to see if there is some problem with volume of the data and found that I could run that same batch and not get the error
Please see code below
regards pete

Rich (BB code):
 Dim Q As String
Rich (BB code):
Dim aurl As String
Dim EXP
                         Sheets("CONTROL").Select
           Q = Worksheets("CONTROL").Range("C2").Value ' pass AUTHOR in from other process
aurl = "http://api.alibris.com/v1/public/search?wquery=" & Q & "&apikey=xxxxxxxxxxxxxxxxxxx"
                      Set EXP = CreateObject("InternetExplorer.application")
                                 EXP.Visible = True
                                      EXP.Navigate (aurl)          
                  Application.Wait (Now + TimeSerial(0, 0, 4))
                                  SendKeys "^a"
                                  SendKeys "^c"
                 Application.Wait (Now + TimeSerial(0, 0, 3))
                         Worksheets("INCOMMING_DATA").Select     
                       Worksheets("INCOMMING_DATA").Range("A1").Select
   ActiveSheet.PasteSpecial Format:="Text", LINK:=False, DisplayAsIcon:= _
       False
 
<o:p></o:p>
End Sub 
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,332
Messages
5,769,470
Members
425,552
Latest member
learnerrr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top