Mark,
Just a note about using Copy; -you can assign one cell's value to another cell
(or range) directly without incurring the extra overhead associated with
Copy/PasteSpecial, and speed up your process by orders of magnitude...

Dim wsSrc As Worksheet, wsTgt As Worksheet

Set wsSrc = ActiveWorkbook.Sheets("Sheet1")
Set wsTgt = ActiveWorkbook.Sheets("Sheet2")

wsTgt.Range("A2:A15") = wsSrc.Range("B7")

This next line I don't understand because you are assigning a range to a single
cell without resizing it to match the number of cells in the source range...

wsTgt.Range("B2").Resize(rows?, cols?) = wsSrc.Range("A9:A22")

...where wsSrc has 14 cells and so wsTgt needs to be resized as follows:

wsTgt.Range("B2").Resize(14, 1) = wsSrc.Range("A9:A22")
or
wsTgt.Range("B2").Resize(1, 14) =
Application.Transpose(wsSrc.Range("A9:A22"))

...where the latter puts a vertical range into a horizontal range.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion