VBA to Copy All; Paste within specified region

asurapan

Board Regular
Joined
Jul 12, 2005
Messages
84
THis should be quite simple...

I have created a templated spreadsheet that calculates a bunch of metrics, formats data, and creates reports. we will call the Workbook1.

I get the raw data from a web-based system's exported report. We'll call this Workbook2.

Currently, I copy from Workbook2 and paste it into Workbook1.

What I want to do, is make it so so that I can Copy All from Workbook2 and then when I put the cursor in cell A! and Paste, it will only paste the values in cells A1:BK1000. (in other words, the copied region is actually larger than the pasted region.)

So in a nutshell, I would have data for cells A1:IV65536 in my clipboard, but when I paste into my main spreadsheet, I only want A1:BK1000 to be filled.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hit Ctrl-a, Ctrl-c. To "Copy All" from the active worksheet.
Position your cursor in cell A1 of the worksheet you want to copy to.
Right click and choose Paste Special | Values | OK.

If you want to maintain formating:
Hit Ctrl-a, Ctrl-c. To "Copy All" from the active worksheet.
Position your cursor in cell A1 of the worksheet you want to copy to.
Ctrl-v to paste,
then Right click cell A1 and choose Paste Special | Values | OK.
 
Upvote 0
actually I dont want all the data to be copied. I want them to do Ctrl-A and then when they do Ctrl-V it ONLY pastes the data into cells A1:BK1000. the other data should not be pasted. Selecting ranges is not an option. Must be done by Ctrl-A and Ctrl-V functions.
 
Upvote 0
Ctrl-A and Ctrl-V are designated HotKeys that, respectively, Copy All and Paste whatever is in the clipboard. You can not selectively Paste what is in the clipboard.
What you need to do is only put in the clipboard what you want to paste. The following code will do just that.
Code:
 Sheets("Sheet1").Range("A1:BK1000").copy Sheets("Sheet2").Range("A1")
Change the sheet names to suit.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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