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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

asurapan

Board Regular
Joined
Jul 12, 2005
Messages
84
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.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Forum statistics

Threads
1,181,657
Messages
5,931,265
Members
436,785
Latest member
KingGideon

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
Top