Recording a macro

[Leslie Isaacs]

Hello All

I need a macro that will do the following:

1) increase the selected cell range by including the 3 cells immediately
below the initially-selected cell
2) copy the selected cells (there will be four of them)
3) go to the cell that is 2 cells to the right of the initially-selected
cell
4) paste-transpose

So for e.g. with:
A1 = "Green"
A2 = "Blue"
A3 = "Red"
A4 = "Yellow"

and all other cells empty, if I start the macro with A1 selected, I want it
to result in:

C1 = "Green"
D1 = "Blue"
E1 = "Red"
F1 = "Yellow"

I have tried to do this by recording the macro, but although the
paste-transpose works fine, the cell references are always absolute -
whereas I need to make them relative (i.e. "go down 3 cells" rather than "go
to A4").

What I have so far is:

Range("A1:C4").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Hope someone can help
Many thanks
Les

 

[Jim Rech]

When you start recording a macro the Stop Recording toolbar should appear.
The second button on that is Relative Reference. You should click that to
get something much closer to what you want.

 

[Guest]

Sub CopyData()
Selection.Resize(4).Copy
Selection.Offset(0, Selection _
.Columns.Count + 1).Resize(1, 4) _
.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
End Sub

worked for me

 

[Leslie Isaacs]

Jim and Tom - many thanks indeed - both of your suggestions worked!
Much appreciated.
Les