Copy/paste user-selected range to/from array

I'm not sure an array is necessarily the best way to do this, so I'll try to explain what I'm trying to do, then how I'm trying to do it...

So on Worksheet 1, user has a pivot table with a list of models:

Model	Info	More info
Apple101	Some info about this model	Some other info about this model
Apple103	...	...
Banana105	...	...
Celery105	...	...
Orange99	...	...
Lettuce31	...	...

On Worksheet 2, a series of formulas provides profitability info on whatever models the user chooses to include.

Model	Cost	Sell	Profit
Apple101	100 (defined by VLOOKUP formula)	125 (manually defined by user)	25 (formula)
Apple103	50	55	5
Celery105	75	125	50
etc	etc	etc	etc

The user must be able to copy some models of interest from the list in worksheet 1, and add them, with relevant formulas, to worksheet 2.

E.g. user goes to Worksheet 1, highlights and copies cells "Orange99" and "Lettuce31". User goes to Worksheet 2, selects the next available row to place them in and pushes a button. The result is that this gets appended to Worksheet 2:

Orange99	200 (defined by VLOOKUP formula)	(to be manually defined by user)	5 (formula)
Lettuce31	100 (defined by VLOOKUP formula)	(to be manually defined by user)	125 (formula)

From the user's point of view it's similar to a copy-paste, except rather than "CTRL-C, select cell in Worksheet 2, CTRL-V" the user doing "CTRL-C, select cell in Worksheet 2, hit Macro button". The effect is it 'pastes' in the copied models and populates the formulas in each row.

I was trying to do this through an array:
- User manually copies cells in Worksheet 1
- User goes to Worksheet 2 and selects cell in row to place copied models
- User hits button to trigger macro
- Macro creates array based on copied cells in Worksheet 1
- Macro loops through array; for each model in the array it populates rows in Worksheet 2 as:

Array(1)	formula	blank	formula
Array(2)	formula	blank	formula
Array(n)	formula	blank	formula

The way I understand it, I need to define the array's as the number of cells in the user's selection (it may be 1 model, it may be 50), so I was using selection.count to define the array size. The problem is that when the user goes to Worksheet 2 and selects a cell they'd want to "paste" the models into, it changes selection.count to 1 (i.e. the currently active cell).
So: is there a way to differentiate between "copied" cells and "selected" cells? How can I define the array size based on 'number of cells copied' instead of selection.count?

Or am I making this way more complicated than it needs to be?

Thanks!

Thanks! This works great for appending to the bottom of the Worksheet 2 list. I'd forgotten that PasteSpecial redefines the Selection, so that essentially lets me do the same thing as the array would have.

Follow-up question: is there a way to insert blank rows while a selection is copied? When I use ActiveCell.Offset(0).EntireRow.Insert, it inserts the number of rows I want, but fills all cells with the copied information.

I'm trying this:

Please Login or Register  to view this content.

The aim is to let the user append the copied models list anywhere in the existing list without overwriting any existing data (new data should just push all existing data down)