I have a worksheet "Orderform" containing a range B8:B12 and a variable
number of rows starting A17:D17, A18:D18, etc.

I need to copy this data over to another sheet "Orders" in a different
workbook such that the range B8:B12 is transposed and pasted at the
beginning of each row and the end of each row is a straight copy from
"Orderform". So it ends up like this:

B8 B9 B10 B11 B12 A17 B17 C17 D17
B8 B9 B10 B11 B12 A18 B18 C18 D18
B8 B9 B10 B11 B12 A19 B19 C19 D19
etc

I've written a macro to do this (see below) but it seems a bit messy.
I'm new to this and sure there's a more efficient way of doing it. In
particular I'm thinking there must be a way to combine the vertical and
horizontal ranges and pasting in one go rather than doing it in two
chunks as I have...any advice much appreciated


Workbooks("order.xls").Sheets("Orderform").Activat e
Dim rw As Long
rw = 17

Do Until ActiveSheet.Cells(rw, 1) = ""

Range(Cells(8, 2), Cells(12, 2)).Select
Application.CutCopyMode = False
Selection.Copy

Workbooks("master.xls").Sheets("Orders").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True

Workbooks("order.xls").Sheets("Orderform").Activat e
Range(Cells(rw, 1), Cells(rw, 4)).Select
Application.CutCopyMode = False
Selection.Copy

Workbooks("master.xls").Sheets("Orders").Activate
Range("F1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Workbooks("order.xls").Sheets("Orderform").Activat e

rw = rw + 1

Loop