may be this ?

Sub swap()

'Rng = Cells(Rows.Count, "D").End(xlUp).Row

'For i = 2 To Rng
i = 16
Range("K" & i & ":N" & i).Insert xlToRight
S1 = Range("C" & i & ":D" & i).Value
S2 = Range("K" & i & ":L" & i).Value
Range("C" & i & ":d" & i) = S2
Range("K" & i & ":L" & i) = S1

'Next i

End Sub



On Aug 22, 9:36*am, "Graham Feeley"
wrote:
Ok first of all I thank you for your response and promptly I may say.
Ok I explained it all wrong, I think u mean to move columns.
It is rows I am working in
and in fact it will be always the same
I have columns A through to M
I wish to move K16:L16 to the right 4 columns (or saved to variants)
then move C16:C16 to K16:L16 then move K16:L16 to C16:C16
in other words it swaps 2 cells in the same row
I thank you for your help and I have save your code for futher reading
Regards
Graham

"JLatham" wrote in message

...

Here you go:

Sub SwapCellPairs()
'assumes you have selected the
'cell at the far left of a group
'to be swapped, and that the
'"distance" between cells to be
'swapped is always the same
'
*'how many columns from left-most of
*'first group to first one in 2nd group?
*'Example first group is in columns A and B
*'second group is in E and F
*'it is 4 columns from A to E
*Const firstGap = 4
*Dim firstValue As Variant ' temporary holding
*Dim secondValue As Variant ' temporary holding
*'save the first group's values
*firstValue = ActiveCell.Value
*secondValue = ActiveCell.Offset(0, 1).Value
*'move second group into 1st group's cells
*ActiveCell.Value = _
* ActiveCell.Offset(0, firstGap).Value
*ActiveCell.Offset(0, 1).Value = _
* ActiveCell.Offset(0, firstGap + 1).Value
*'move the 1st group over to where the
*'2nd group is
*ActiveCell.Offset(0, firstGap).Value = _
* firstValue
*ActiveCell.Offset(0, firstGap + 1).Value = _
* secondValue
*'all done!
End Sub

To put the code in your workbook, open it up and press [Alt]+[F11] to open
the vB Editor. *In the VBE choose Insert -- Module and copy and paste the
code above into that. *Make any change you need to to the Constant value
named firstGap. *Close the VBE.

Back in the worksheet view, choose Tools -- Macro -- Macros and
highlight
the name of the macro and use the [Options...] button to assign it a
shortcut
so that you can use it quickly.

After that, all you have to do is select the left-most cell on a row wher
you want to swap things and hit your shortcut key combination to swap the
two
groups. *If you accidentally swap a wrong pair, just do it again to put
them
back the way they were.

Enjoy!

"Graham Feeley" wrote:

Hi, to explain what I am trying to achieve is this.
I have several columns and rows of data and I wish to move certain cells
around in the same row.
I have made a macro that moves 2 cells to a blank space, move 2 other
cells
into its cells and the moves the first 2 cells in its place

however the macro alway returns to the cell address where I made the
macro
I would like it to commit what ever row i am in
Hope u can understand what I am trying to achieve.

1 ONEMORENOMORE * * * * * * * * * * * * *3 DELAGO BOLT
3 *GRAHAM * * * * * * * * * * * * * * * * * * * * * * 8 WILLIAM
The macro in this case would swap n08 william with 3 Graham
Regards
Graham