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

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

Fyi, Cells always refers to the active sheet

Range(Cells(8, 2), Cells(12, 2)).Select

You do not have to select a range to copy it or paste to it.

Application.CutCopyMode = False

You do not have to clear the clipboard before using it.

So...

Range(Cells(8, 2), Cells(12, 2)).Copy
Workbooks("master.xls").Sheets("Orders").Range("A1 ").End(xlDown).Offset(1).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:=False,
Transpose:=True


--
Jim
wrote in message
ps.com...
|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
|