Macro to move one cell in column A to a cell in Column B

[mmlehmann]

I am attempting to create a Macro that allows me to cut a cell from column A and place in column B. For example I need a item cut from Cell A162 and pasted in Cell B161. I need the same Macro to cut cell A165 and pasted in Cell B164 ect... This will need to continue until cell A1374 is cut and pasted in cell B1373.

 

[mrmmickle1]

Hey, welcome to the board!

Something similar to this should work:

[COLOR=#0000ff]Sub[/COLOR] Test()

    i = 161
   [COLOR=#0000ff] For[/COLOR] X = 162 [COLOR=#0000ff]To[/COLOR] 1374 [COLOR=#0000ff]Step[/COLOR] 3
        Range("A" & X).Cut
        Range("B" & i).Select
        ActiveSheet.Paste
        i = i + 3
 [COLOR=#0000ff]   Next[/COLOR] X

[COLOR=#0000ff]End Sub[/COLOR]

Copy A162 ---> Pastes to B161
Copy A165 ---> Pastes to B164
Copy A168 ---> Pastes to B167 ....etc

 

[mmlehmann]

Thanks for responding. When I ran that Macro it cut the cell from column A162 and pasted it into B162. I needed it to be moved from A162 to B161.

I appreciate your help!

 

[mrmmickle1]

It seems to work fine for me. What code are you using?:

Before:

Excel 2012
	A	B
161
162	A162 TEXT
163
164
165	A165 TEXT
166
Sheet1

After:

Excel 2012
	A	B
160
161		A162 TEXT
162
163
164		A165 TEXT
165
Sheet1

 

[mmlehmann]

I did not use any code. I copied and pasted the macro into the excel document. I am new at Macros.

 

[mrmmickle1]

Sure no problem:

Look at this link for instructions: CLICK HERE

Use the code on a copy of your data. The changes macros make to your workbook are not reversible.

 

[mmlehmann]

Perfect that worked! Thanks for your help!

Matthew

 

[mrmmickle1]

Your Welcome! Glad to help out.

 

[Rick Rothstein]

Hey, welcome to the board!

Something similar to this should work:

[COLOR=#0000ff]Sub[/COLOR] Test()

   i = 161
   [COLOR=#0000ff] For[/COLOR] X = 162 [COLOR=#0000ff]To[/COLOR] 1374 [COLOR=#0000ff]Step[/COLOR] 3
        Range("A" & X).Cut
        Range("B" & i).Select
        ActiveSheet.Paste
        i = i + 3
 [COLOR=#0000ff]   Next[/COLOR] X

[COLOR=#0000ff]End Sub[/COLOR]

Copy A162 ---> Pastes to B161
Copy A165 ---> Pastes to B164
Copy A168 ---> Pastes to B167 ....etc

Just so you know, you can specify what to cut and where to cut it to in the same statement...

[COLOR=#0000ff]Sub[/COLOR] Test()

    i = 161
   [COLOR=#0000ff] For[/COLOR] X = 162 [COLOR=#0000ff]To[/COLOR] 1374 [COLOR=#0000ff]Step[/COLOR] 3
        Range("A" & X).Cut Range("B" & I)
        i = i + 3
   [COLOR=#0000ff] Next[/COLOR] X

[COLOR=#0000ff]End Sub[/COLOR]

And if you observe that the "copy to" row is always one less then the "copy from" row, you can simplify your code down to this...

[COLOR=#0000ff]Sub[/COLOR] Test()

    [COLOR=#0000ff]For[/COLOR] X = 162 [COLOR=#0000ff]To[/COLOR] 1374 [COLOR=#0000ff]Step[/COLOR] 3
        Range("A" & X).Cut Range("B" & X - 1)
    [COLOR=#0000ff]Next[/COLOR] X

[COLOR=#0000ff]End Sub[/COLOR]

 

[mrmmickle1]

Thanks Rick! I appreciate the pointers. I'm always after ways to make my code more succinct!!!