Solved: Test before paste special

[Danny]

I am always having to use paste special and would like to have a ctrl+q command to do this. The only problem is that the data is not always the same. Sometimes I want to paste just the values (when copying info from formulas) and other times I want to paste as text (when copying from an access query). Therefore I would like a code that would test what kind of data has been copied in order to decide how to paste special. any ideas would be appreciated.
Here are the two types of paste special:


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

' type 2
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

[Bob Phillips]

You need to specify the rules that determine what pasting is done. Results of an Access query is no good, it has to be based upon the data.

[Danny]

Xld,
I don't know how to determine the rules. The only thing i can think of is that the window that comes up changes when paste special is selected. If the data copied is from excel then the options are to paste all, formulas, values etc. And if the data is copied from any other source then the options to paste are text, unicode text etc. (these options seem to change) but i will always want to paste either the values or text.
Thanks,
Danny

[mikerickson]

This isn't very bullet proof, but might a test like this work for you?

If Application.CutCopyMode = 0 Then
MsgBox "Clipboard is from non-Excel source"
Else
MsgBox "Clipboard is filled with data from Excel"
End If

[Danny]

This may not be a very good way to do this, but it seems to work.... so far. Any reasons why this may cause headaches?


Sub pstspecial()
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Format:="Text"
End Sub

[Bob Phillips]

You are lucidly showing the dangers of using On Error Resume Next because there is no Format argument to the PasteSpecial method but you don't find that out because you suppress errors.

[Danny]

xld,

Could you recommend a better way to do this, or are there too many possible data types?

[Bob Phillips]

Not that there are too many type, but more you are trying to do something you can't. What do you mean by paste as text, you can paste formats, formula, vaues and so on, but not as text. And you previously stated that you have no rules, without which it is difficult to do anything, it is either all or nothing then.

[Danny]

If you copy this sentence from your browser and try to paste special into excel, the options (for me) are as text, html, unicode text .... From what i can tell the only time that you can paste special with the options: formulas, formats etc is when the data that has been copied is from excel. I will want to paste either the text or the data everytime. Hope that exlpains it better.... (I am not trying to sound dense)