VBA to Copy All; Paste within specified region

[asurapan]

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.

 

[Datsmart]

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]

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]

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.

 Sheets("Sheet1").Range("A1:BK1000").copy Sheets("Sheet2").Range("A1")

Change the sheet names to suit.