Get From Clipboard

[Mark S]

I am trying to get text from copied cells through the clipboard and after
reading the text into an array and some manipulation to the text be able to
write the new text back to a range of cells.

I have written everything except for the part that writes it back and it
works fine in the VB editor. But if I try to run the macro (sub) from Tools
on the worksheet I get run-time error DataObject:GetText Invalid FORMATETC
structure.

My code is centered on the following lines:
dObj.GetFromClipboard
s = dObj.GetText(1)

The error occurs on the GetText line.

Reference to Microsoft Forms 2.0 Object Library is checked.

Again, it works fine from the editor and I am able to Debug.Print the
elements of the array without a problem. But if I run the same Macro from
the worksheet, then no joy.

Any ideas? What if I put the code into a function and just have a the sub
procedure making the Call and then writing the results back where I want it?

Any help is appreciated.

 

[Mark S]

Now I seem to have an additional problem.

What I am trying to do is copy a list of select personnel names and paste
their initials to a range of cells. This will occur over two separate
workbooks.

I thought I tested this, but when copying the cells all the cells from the
first cell to that last cell selected are copied to the clipboard, not just
the selected cells.

Ideally, I want to be able to mark specific names from a list, manually move
to a position in another workbook and then insert the initials from the
names. I thought going through the clipboard would be the easiest way for
the user to select the names.

I am guessing that when copying to the clipboard, the entire range is copied
but selected cells are marked somehow. Also included would be everything
from the Paste Special dialog (formatting, formulas, etc.).