VBA code to Copy multiple items from Clipboard

3link

Board Regular
Joined
Oct 15, 2010
Messages
138
I want to programmatically copy three ranges from one spreadsheet and paste those ranges into the same cell addresses of another spreadsheet. The code for copying the ranges looks like this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Rich (BB code):
Sub Button256_Click()
Range("O16:W1289").Copy
Range("Z16:AG1289").Copy
Range("AK16:AR1289").Copy
End Sub
<o:p></o:p>
<o:p></o:p>
This successfully puts the three ranges on the clipboard. My problem has been creating a macro on the spreadsheet to be pasted that would paste each range into the corresponding cells. Specifically, I don't know how to tell clipboard to paste this clipboard item here and that clipboard item there. <o:p></o:p>
<o:p></o:p>
It is important that Range O16:W1289 of the copied spreadsheet be pasted in range O16:W1289 of the receiving spreadsheet. Thus, I need clipboard to be able to distinguish between the three. I was hoping that clipboard might do this by the order that items were copied, but I can't seem to get that working. <o:p></o:p>
<o:p></o:p>
Here's a sample of what I have tried on the receiving spreadsheet:<o:p></o:p>
<o:p></o:p>
Rich (BB code):
Sub Button255_Click()
Range("O16").Select<o:p></o:p>
Selection.PasteSpecial Paste = xlPasteValues<o:p></o:p>
End Sub
<o:p></o:p>
<o:p></o:p>
I get a range error. I'm confident that the problem is that I'm not telling VBA which of the three items on the clipboard it should paste. How do I tell it that?<o:p></o:p>
<o:p></o:p>
Thank you in advance.<o:p></o:p>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
One Macro will do it -- Note I changed your cell references (you need to change back to you cell refs - Also I currently have Sheet2 as destination WS; change that also if
necessary.

Jim

Code:
Sub Foo()
ActiveSheet.Range("A1:C3").Copy Destination:=Worksheets("Sheet2").Range("A1")
Range("D5:J9").Copy Destination:=Worksheets("Sheet2").Range("D5")
Range("G11:H15").Copy Destination:=Worksheets("Sheet2").Range("G11")
End Sub
 
Upvote 0
Jim,

Thank you for your response. That takes me one step closer. However, I'm afraid that I didn't properly explain what I'm doing. When I said 'spreadsheets', I intended to refer to different workbooks -- not different sheets within the same book. Is there a way to modify your code to achieve this?

Thanks,
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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
Back
Top