Thread: Copy to clipboard

Hi guys
after a way to copy a group of cells to clipboard (a21 to c24)
and leave it there until i need it
is this possible?

Good morning excellentexcel

Originally Posted by excellentexcel

after a way to copy a group of cells to clipboard (a21 to c24) and leave it there until i need it
is this possible?

The clipboard is a Windows object, not an Excel object so referencing it is not as easy as you might think.

Chip Pearson has an excellent article on the issue here with code examples and workbooks for download.

HTH

DominicB

Thanks dominic
not quite sure how i can adapt that though to suit my needs?i just want to press the button and it will store them cells on my clipboard.
Tried doing it using macro recorder to no avail

Hi excellentexcel

Originally Posted by excellentexcel

Thanks dominic
not quite sure how i can adapt that though to suit my needs?i just want to press the button and it will store them cells on my clipboard.
Tried doing it using macro recorder to no avail

The macro recorder won't work as it's a Windows object.
Have a look at the attached example. type something in A1, click the button and then manually paste it somewhere else.

HTH

DominicB

HI DOMINIC
YORU WORKS GREAT BUT WHEN I CHANGE IT TO SUIT
AS IN:-

HTML Code: 

Sub COPYTOADMINBASE()

Dim DataObj As New MSForms.DataObject
    Dim S As String
    S = Range("A21:C24").Value
    DataObj.SetText S
    DataObj.PutInClipboard
End Sub

I GET RUN TIME ERROR 13
TYPE MISMATCH

AND IT HIGHLIGHTS THE LINE

HTML Code: 

S = Range("A21:C24").Value

Hi excellentexcel

OK. The clipboard can only hold one item at a time - it can be manipulated into copying multiples into there, but you are going to have to manually keep track of the size of the array (R x C) and the items in there, and (possibly) a delimiter character. Do you really have to use the clipboard?

Take a look at the code below - it's a bit rough at the moment, but it's a starting point. Highlight a selection (say 10 rows x 3 columns) and run the copy1 macro.

Now highlight a blank range (different shape - same number of cells, say 2 rows x 15 columns) and run the macro paste1.

As I say, it needs refining, perhaps you would like it to keep tha shape of the selection, so you only need to select 1 cell and paste from there - but it's a starting point. Would this be of any use to you?

Dim items()

Sub copy1()
a = Selection.Count
counter = 1
ReDim items(a)
For Each usrcell In Selection
items(counter) = usrcell.Value
counter = counter + 1
Next usrcell
End Sub

Sub paste1()
counter = 1
For Each usrcell In Selection
usrcell.Value = items(counter)
counter = counter + 1
Next usrcell
End Sub

HTH

DominicB

cheers for your efforts dominic
i have to be honest to you and say i cant get my head round that
perhaps it will be easier if i just do it as i have done for the last 6 months
thanks all the same