copy several items & paste several items?

  • Thread starter Thread starter Ian Elliott
  • Start date Start date
I

Ian Elliott

Thanks in advance.
I want to copy several items into the Office Clipboard
(Edit>Office Clipboard...) and then paste those several
items into the active sheet (using VBA). (I believe up to
12 items can be stored in the Office clipboard) So rather
than copying one item, activating the sheet, pasting it,
then activating the sheet to copy from, copying, then
activating the sheet I want to paste to one more time, and
pasting it, the macro would:
select an item
copy to Office clipboard
select another item
copy to Office clipboard
activate another sheet
select a range
pastespecial an item
select another range
pastespecial another item
I think this would save time.
I can do this without a macro (and I recorded it but
copying/pasteing appears not to differentiate between
items in the clipboard, so I did not know how to transfer
this to VBA), but is this possible with VBA?
Thanks again.
 
Otto,
Thanks,
I want to copy two things to the Office clipboard and then
paste the two things back.
Like this (note, this won't work, but symbolizes what I
want to do):
range("a1").copy
range("a2").copy
cell(2,2).select
activecell.paste 'paste a1 here
cell(3,3).select
activecell.paste 'paste a2 here

This is possible with the user doing: Edit>Office
Clipboard... and copying the two and pasting the two, but
is this possible with VBA?
 
Ian
You can still do what you want without getting into the clipboard and do
it faster. The format of the VBA statement to copy and paste something is:
Sheet1.Range("A1").Copy Destination
Destination is the sheet and range where you want it pasted. Note that
there is a space between Copy and Destination. Also note that nothing is
selected on either end. If either the source sheet or the destination sheet
is always the same, then the above can be shorter still. HTH Otto
 
What Otto was telling you is that you can transfer data from one place
to another without selecting the data to copy or the destination to
paste.

Worksheets("Sheet1").Range("A1:D4").Copy _
destination:=Worksheets("Sheet2").Range("E5")

Obviously, you'll have to readjust to suit your needs.

---------------------------------------------------------------------------------------

In most cases in which I'm transferring data (no formats), I don't use
the clipboard. I either fill an array or assign the data, but that's
for another day.

HTH
Paul
 
Back
Top